(dba beginner) As an exercise, I test different methods to restore all or part of a database.
I have 2 dumps of a table: the first one in sql format, the second one in custom format. So I use psql in the first case, pg_restore in the second.
So far, I haven't been able to restore the table. I would like to create it in another schema or database, so that I can compare the one that has been restored and the one that exists, for example.
The table has a primary key, triggers and constraints.
Does my goal make sense? If yes, what is the correct order/track? Thanks

EDIT : I tried in both cases (sql, backup) to restore the table in another database. table in a database test :

pg_restore -h localhost -p 5433 -U admin -d test -v c:\blabla.backup

14 errors beginning with the table can't be created because the sequence of the primary key doesn't exist.

psql -d test - h localhost -p 5433 -U admin -f c:/blabla.sql

same message about sequence then error : relation _mytable_ doesn't exist then psql:c:/.../: commande \N invalide

Also, I created in the db test the schema my table belongs to before trying to restore. It failed as well.

I did not use -a option because the empty table has to created before. Here's what I did the most.

  • If you have the requirement to be able to restore a single table, you have to use the custom format, this is not possible with the SQL format. Nov 15, 2019 at 12:13
  • Did you dump just the table, or the whole database, for the sql format dump? Are you trying to restore the triggers/constraints, or just the data? What have you tried (like, all the command switches used) and what errors did you get?
    – jjanes
    Nov 15, 2019 at 12:50
  • I dumped just a table on the both cases. I will edit the first message to write some commands I wrote.
    – Leehan
    Nov 15, 2019 at 13:09
  • Did you try to create the sequence by hand?
    – eckes
    Nov 15, 2019 at 16:17
  • nope I did not, I have the feeling that after this one there will be an other error like before : schema doesn't exist, I create the schema, then seq doesn't exist, I will create the seq ans there will be an other error. I can still try it anyway.
    – Leehan
    Nov 18, 2019 at 14:37


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.