Question 113924 in PostgreSQL Question posted in
The official documentation can be found here.

How To Restore Specific Schema From Dump file in PostgreSQL?

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search