Is it possible to import one database from pg_dumpall

Question

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 1

This small script will do it.

    #!/bin/bash

    [ $# -lt 2 ] && { echo "Usage: $0 <postgresql dump> <dbname>"; exit 1; }

    sed  "/connect.*$2/,\$!d" $1 | sed "/PostgreSQL database dump complete/,\$d"

(It writes to the STDOUT you have to pipe it to a file.)

Answer 2

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