CODEWITHSUNDEEP
postgresqlpg-dump
Simon Campano
Simon Campano

Reputation: 97

Convert PostgreSQL SQL dump to PostgreSQL custom-format dump

My use case is the following:

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.

Upvotes: 2

Views: 8104

Answers (1)

nbwoodward
nbwoodward

Reputation: 3156

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

Upvotes: 6

Related Questions