Copy a live postgres database

Why this is not built in

Being able to import a .sql would have been nice, but there's one problem. Databases in a relational database (like postgres) need a name since a server can host many databases.

So instead of trying to bend sider into something it's not made to do (knowing syntaxes of individual database - and knowing in detail how the dumps works) you get the second best thing - an empty database where you can import to your hearts content and then save it off as a snapshot for future uses.

First - create an empty snapshot to fill with the dump

Start sider with an empty snapshot: sider snapshot add -e postgres <snapshot-name>

Let it run and follow one of the two steps below:

Either - there is no .sql dump but you have the rights to run pg_dump

With the empty sider snapshot, simply pipe in the output to pqsl (using the -C pg_dump command to create the database). pg_dump -h localhost -p 5433 -U postgres -C shakespeare | psql -h localhost -U postgres

When done shut down the sider snapshot with ctrl+c.

If you have pv installed, you can get some gauge on the progress of the download. Also you can enable compression on pg_dump on the server (add how to).

Or - there's a .sql dump out there

If you know the dump does not have a create database statement first in the file you need to do it yourself first against the now empty sider snapshot: createdb -h localhost -U <postgres-user> <database-name> Then import the dump psql -h localhost -U <postgres-user> <database-name> < dump.sql

If you know the dump contains a create database & use database commands first - simply hit psql -h localhost -U <postgres-user> < dump.sql.

When the import is done, shut down sider with ctrl+c and voilá! The import is now stored away safely as a snapshot.