Convert PostgreSQL SQL dump to PostgreSQL custom-format dump

Question

My use case is the following:

I have PostgreSQL dumps made with pg_dump -F p (plain-text SQL script file), which I have no control over how they're made
I like pg_restore flexibility (--no-owner, --data-only, --clean, etc)

I know I should use psql with plain-text SQL dumps, but is there any way to convert the SQL dump into a PostgreSQL custom-format dump so I can use pg_restore, or some way of getting all its options?

Thanks for the attention.

No, you can not convert it. The only way to create a dump in custom format is to use pg_dump -F c — user330315
– user330315, Commented Sep 3, 2018 at 14:00
I stand by with mr Horse with no name, u cant really do that, except for dump obviously. — Thomas J.
– Thomas J., Commented Sep 3, 2018 at 14:02

nbwoodward · Accepted Answer · 2018-09-03 17:02:08Z

6

As the other answers/comments mentioned, you can't convert it directly. What you should do is create a temporary database from the sql backup file and use pg_dump to make a custom format from it.

Check that the sql backup is not a "clean" restore file, meaning it doesn't drop the database at the top of the file. If it is, remove the "DROP DATABASE dbase"..

Create a temp database:

psql
CREATE DATABASE mytempdb;
\q

Then restore the backup you have to it:

psql mytempdb < my_database_backup.sql

Note: This is why you do NOT want the "drop database..." line in the top of the sql file. It will drop your original database instead.

Dump it as custom format:

pg_dump mytempdb -Fc -f custom_format.dump

answered Sep 3, 2018 at 17:02

nbwoodward

3,1961 gold badge22 silver badges29 bronze badges

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

1 Comment

bitinerant Over a year ago

It would be good to delete this temporary database when you're done: psql --command="DROP DATABASE mytempdb;"

Collectives™ on Stack Overflow

Convert PostgreSQL SQL dump to PostgreSQL custom-format dump

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related