postgresql pg_dumpall restore one database

Question

My environment:

# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.3 (Maipo)
# rpm -q postgresql
postgresql-9.2.18-1.el7.x86_64
# 

I used following PostgreSQL: Documentation: 9.6: pg_dumpall to take dump all databases

$ pg_dumpall> db.out

from what I understand, following will restore all databases:

$ psql -f db.out postgres

my goal is, I need to restore just a one (single) database from that entire dump, how does one accomplish this task?

• András Váczi: What exactly you need from the output of pg_dumpall, apart from the DB in question? Isn't it easier just to dump a single DB using pg_dump?

Answer 1

Your dump is in SQL format. Just open your db.out file remove what's for other databases.

• alexus: that would be too much, i want better solution then editing dump file, what's other way of accomplishing same thing? perhaps changing pg_dumpall to pg_dump? but then I need to keep track of each database name, which also not ideal...
• Arkhena: Then you have to use pg_dump for each and every database you want to dump to a file. You can list all you databases with psql. Don't forget to use pg_dumpall with -g option to save roles and tablespaces.