Import postgresql database dump

Question

I have the following work flow which I'd like to automate with short PHP script.

Download gzfile (with db dump) from specific URL (potentially FTP).
Decode the file to txt.
Import the txt to local postgre with psql (using cmd).

Now I have 2 questions:

What is the best way to pass the gunzipped file to pg_query?
I get an error when PHP reaches this line:
COPY rf (datum, id_emailu_op, recency, frequency) FROM stdin; 2011-08-29 8484 3 1. Can the stdin be a problem?

Thank you all!

gzcat dump.gz|psql at a shell prompt would decompress the .gz file and sent the output directly to postgres. — Marc B
– Marc B, Commented Aug 30, 2011 at 14:35
A short Bash script is probably simpler, depending on what your needs are. — Brad Koch
– Brad Koch, Commented Sep 2, 2011 at 3:09
I would recommend dumping using pg_dump -Fc and restore using pg_restore. — Kenaniah
– Kenaniah, Commented Sep 28, 2011 at 18:48

hsikcah · Accepted Answer · 2012-02-04 23:40:18Z

2

A pg_dump file is meant to be imported via psql. You can load the file contents in, and even decompress it with php, then open a pipe to psql writing data out to that process (assuming you are on a unix machine). When psql is executed in this way as far as it's concerned the data you're writing via your php script is coming in via stdin.

answered Feb 4, 2012 at 23:40

hsikcah

6016 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import postgresql database dump

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related