How To Restore Specific Schema From Dump file in PostgreSQL?-CodePudding

I have a dump file (size around 5 GB) which is taken via this command:

pg_dump -U postgres -p 5440 MYPRODDB > MYPRODDB_2022.dmp

The database consists multiple schemas (let's say Schema A,B,C and D) but i need to restore only one schema (schema A).

How can i achieve that? The command below didn't work and gave error:

pg_restore -U postgres -d MYPRODDB -n A -p 5440 < MYPRODDB_2022.dmp

pgrestore: error: input file appears to be a text format dump. please use psql.

CodePudding user response：

You cannot do that with a plain format dump. That's one of the reasons why you always use a different format unless you need an SQL script.

CodePudding user response：

If you want to stick with a plain text dump:

pg_dump -U postgres -p 5440 -n A MYPRODDB > MYPRODDB_2022.dmp


psql -U postgres -d MYPRODDB  -p 5440 -f  MYPRODDB_2022.dmp

Though dumping back over the same database as above will throw errors unless you use --clean or its short form -c to create commands to drop existing objects before restoring them:

-c

--clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is ignored when emitting an archive (non-text) output file. For the archive formats, you can specify the option when you call pg_restore.

Probably also a good idea to throw in --if-exists:

--if-exists

Use conditional commands (i.e., add an IF EXISTS clause) when cleaning database objects. This option is not valid unless --clean is also specified.