Making a backup of the structure of a PostgreSQL database

I’m currently extending a web application that uses a PostgreSQL 8 database to store various data. In situations like these you’ll usually have a web server and a database server installed on your development machine so you can test the code there. However, copying all the data in the database is often not practical or desired (in my case it is impossible due to the size of the DB alone).

What I usually want is some manually selected test data sets, so the first thing I need is an empty database on my local database server that has same structure as the one on the production machine — the same tables, users, stored procedures, constraints, etc. — but no data in it.

You can get that from an existing PostgreSQL db using the pg_dump tool. Here’s what I did to make the backup of the DB structure. Note that I switched to the db admin account (UNIX user postgres) before doing that:

root@srv> su postgres
postgres@srv> pg_dump --schema-only --format=custom --file=<filename> <database>

Now you can copy the file to your development box. Before you can restore it, you need to create the database and a user for it though. There are at least 2 ways to do this (and you need only one of them, obviously):

Possibility (a): Using the interactive psql command

postgres@dev> psql
psql> CREATE ROLE <dbuser> WITH LOGIN;
psql> CREATE DATABASE <database> OWNER <dbuser>;
psql> \q


Possibility (b): Using the external tools that come with pg

postgres@dev> createuser --login <dbuser>
postgres@dev> createdb --owner=<dbuser> <database>

Now that the database and user are created, you can restore the dump file:

postgres@dev> pg_restore --dbname=<database> <filename>

You should now have an empty database with the same structure as on the production machine.


About dfspspirit

PhD student in bioinformatics, interested in photography, level design, digital image manipulation, architecture and, of course, bioinformatics.
This entry was posted in coding, databases and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s