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?
3 Answers
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
1 Comment
brock
Glad to hear it is still working for you @user1803784
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.)
1 Comment
KeKru
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"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
3 Comments
Jasen
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.
Andy Gajetzki
That's what I said; he will need to extract the relevant ports from the dump.
Simon Woodside
Doesn't work.
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.
initdba new temporary PostgreSQL instance, restore thepg_dumpalloutput to that, thenpg_dump -Fcjust the desired database so you canpg_restoreit.