How to restore a cluster backup in PostgreSQL?

Question

I am trying to restore a cluster backup in PostgreSQL. I used pg_dumpall and it generated a plain text file with SQL queries. However, when I try to restore it, I get this error:

pg_restore: [archiver] input file does not appear to be a valid archive

I had also received the same error when I had tried to restore databases' backups (generated with pg_dump) but I solved that problem. In that case pg_restore did not recognize plain text with SQL queries, so I changed the command:

pg_dump db_name -Fc -U user -h host -p port > my_db_backup.dump

And wrote instead:

pg_dump db_name -Fp -U user -h host -p port > my_db_backup.dump

This way the generated file was not in plain text, so pg_restore worked well with that.

Now, I am trying the manage the same with pg_dumpall, but there is not a -F param for that one. My code (in Python3, but this command would be the same for any language) is:

pg_dumpall -U user -h host -p port > my_cluster_backup.dump

Any idea of how to manage this? Or at least how to restore a cluster backup in any way? Thank you in advance!

EDIT

If I use psql to restore:

psql -U user -h host -p port -f my_cluster_backup.dump postgres'

It seems to work, a lot of commands are shown in console, but each of them gives a "it already exists" error. I cannot understand because I have two clusters (ports 5432 and 5433) and I guess it is trying to restore in 5432, when I am sure that I am connected to 5433. Anyone had this problem before?

Answer 1

See the example at the end of pg_dumpall doc:

To dump all databases:

$ pg_dumpall > db.out

To reload database(s) from this file, you can use:

$ psql -f db.out postgres

(It is not important to which database you connect here since the script file created by pg_dumpall will contain the appropriate commands to create and connect to the saved databases.)

db.out in this example is a SQL commands script, meant to be fed directly to the psql interpreter.

pg_restore does not work with a plain SQL file as input. Its main/original purpose is to take as input a backup in a non-text format (e.g. pg_dump -Fc) and produce the corresponding SQL file as output, or to restore directly into the database using parallel paths (-j)

When the input is already a SQL script, there's really no point in using pg_restore rather than psql, so presumably that's why the authors didn't bother to support SQL scripts as input to pg_restore.