CODEWITHSUNDEEP
postgresqlpsqlpg-dumppg-restore
James Van Leuven
James Van Leuven

Reputation: 41

psql equivalent for pg_restore for .sql files

I ONLY dump my databases as *.sql files not *.dump files. As a result NONE of the pg_restore commands work. I've been reading through answers and I swear most people have a reading disability lol

I am asking for the equivalent in psql for a common pg_restore commandLine method to restore a database. I have no intention of dumping my databases as *.dump.

my question is this: what is the equivalent to: pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.dump using psql

so...

something along the lines of: psql --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.sql

Upvotes: 1

Views: 5138

Answers (1)

user330315
user330315

Reputation:

With a SQL dump you need to decide whether you want to drop target objects, when dumping the database, not when importing it.

So, you need to use:

pg_dump --clean ....

Then the SQL dump will contain the necessary DROP statements.

Another option is to run drop owned by current_user before doing the import. This however requires that everything is owned by the user doing the import (so you can't run the import as e.g. postgres)

This can be combined with running the SQL dump:

psql -U your_user -d your_db -c 'drop owned by current_user' -f your_dump.sql

Upvotes: 1

Related Questions