Is it possible to import one database from pg_dumpall
I would like to get a database by name from a pg_dumpall and import it into my local postgres. If possible, I'd like to use a different database name on my local machine. Is this possible?
Answer
It doesn't seem like PostgreSQL has a built-in way to do this, so I put a script together that can handle this for me. Here is what I learned, and there are more comments in the gist, but in a nutshell:
pg_dumpall contains several SQL import statements, one for each database on the server.
You can easily find the start and end of each database by searching for these two strings:
\connect databasename and PostgreSQL database dump complete
The contents in between those two strings make up each import. I have omitted the first line from the exports that I create. Using \connect databasename in the top of your script means that the database must already exist. So if you want to import a database under a different name, you can safely remove that first line, and run your import like this:
psql new_databasename < databasename.sql
This does take a long time to run on large databases, so I might refactor it later to speed it up if I need it, but for now it works. It also spits out a postgres.sql export, and I haven't tested importing that one, but if you want to just extract one database from a pg_dumpall, this does the trick.
https://gist.github.com/brock/63830f11c0945f82f9ea
Save the file in this gist to your ~/bin as pg_extract, make it executable, and you can run it by passing the filename of the original sql dump: pg_extract postgresql_dump.sql. You'll have a .sql file for each database in the dump located in your current directory.
EDIT: I've updated the script now so that you can pass the name of the database you want to extract and it will stop there. For example: pg_extract postgresql_dump.sql databasename outputs a single databasename.sql