Restore a postgresql dump (.sql file) without the command line?

Question

Scenario:

I have built a PHP framework that uses a postgresql database. The framework comes shipped with a .sql file which is a dump of the default tables and data that the framework requires.

I want to be able to run the sql file from the client (PHP), rather than the command line, in order to import the data. This is because I have come across some server setups where accessing the command line is not always a possibility, and/or running certain commands isn't possible (pg_restore may not be accessible to the PHP user for example).

I have tried simply splitting up the .sql file and running it as a query using the pg_sql PHP extension, however because the dump file uses COPY commands to create the data, this doesn't seem to work. It seems to be that because COPY is used, the .sql file expects to be imported using the pg_restore command (unless I am missing something?).

Question:

So the question is, how can I restore the .sql dump, or create the .sql dump in a way that it can be restored via the client (PHP) rather than the command line?

For example:

<?php pg_query(file_get_contents($sqlFile)); ?>

Rather than:

$ pg_restore -d dbname filename

Example of the error:

I am using pgAdmin III to generate the .sql dump, using the "plain" setting. In the .sql file, the data that will be inserted into a table looks like this:

COPY core_classes_models_api (id, label, class, namespace, description, "extensionName", "readAccess") FROM stdin;
1   data    Data    \\Core\\Components\\Apis\\Data  The data api    Core    310
\.

If I then run the above sql within a pgAdmin III query window, I get the following error:

ERROR:  syntax error at or near "1"
LINE 708: 1 data Data \\Core\\Components\\Apis\\Data The data api Core...

If it is an actual SQL dump (and not, for example, pg_dump's custom format), you can just run the query into the database — Sami Kuhmonen
– Sami Kuhmonen, Commented Jun 1, 2015 at 9:07
My initial thoughts were the same. I have tried this, using the .sql file, but it's that COPY statement and the data format that causes the issue. Is it maybe because i'm using pgAdmin III? Is the above the pg_dump's custom format that you are referring to? I've added an example above. — Donny Sutherland
– Donny Sutherland, Commented Jun 1, 2015 at 9:58
Looks like it wants data from stdin, I doubt you can call dup from PHP though. Try rewriting the file to insert values — Aleksei Averchenko
– Aleksei Averchenko, Commented Jun 1, 2015 at 10:01
Yeah I tested that too. Inserts work as you would expect. Looking into this a bit further pgAdmin III is doing a pg_dump using the "p" format (plain) which states that it generates a plain-text SQL file. I don't see any options that allow me to change those COPY commands to INSERT commands...it looks like I might need to find a custom solution for this? — Donny Sutherland
– Donny Sutherland, Commented Jun 1, 2015 at 10:40

Donny Sutherland · Accepted Answer · 2015-06-01 11:14:41Z

1

This was a bit tricky to find, but after some investigation, it appears that pg_dump's "plain" format (which generates a plain-text SQL file) generates COPY commands rather than INSERT commands by default.

Looking at the specification for the pg_dump here, I found the option for --inserts. Configuring this option will allow the dump to create INSERT commands where it would normally create COPY commands.

The specification does state:

This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents.

This works for my purposes however, and hopefully will help others with the same problem!

edited Jun 1, 2015 at 11:14

answered Jun 1, 2015 at 10:47

Donny Sutherland

3562 gold badges3 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Restore a postgresql dump (.sql file) without the command line?

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