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?

Cyclo

You'll need to initdb a new temporary PostgreSQL instance, restore the pg_dumpall output to that, then pg_dump -Fc just the desired database so you can pg_restore it. – Ovaritis

Answer 1

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

Cyclo

Thank you sooooo much this saved me a ton. – Beamer

Glad to hear it is still working for you @Beamer – Cyclo

Answer 2

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.)

Glob

This is some very neat use of sed. Thank you! – Babbie

A little modfied, to be sure to match "\connect<whitespaces><dbname>" sed --regexp-extended "/\\\\connect\\s+$1/,\$!d" "$2" | sed "/PostgreSQL database dump complete/,\$d" – Aphrodisiac

Answer 3

No, you will need to extract the relevant portion from the dump. Using pg_dump --format=custom gives you some more flexibility with regards to restores, but I don't think restoring to a different db is possible with that either.

edit: Actually, you can with pg_restore --dbname=foo

Griseldagriseldis

if all you have is an ascii dump-all, a good text editor can be used to cut out the section you want to restore. you'll probably need to use an editor that's neither constrained by line length nor file-size, nor confused by about utf-8. – Inimitable

That's what I said; he will need to extract the relevant ports from the dump. – Griseldagriseldis

Doesn't work. pg_restore: [archiver] input file appears to be a text format dump. Please use psql. – Multi