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
psql> CREATE ROLE <dbuser> WITH LOGIN;
psql> CREATE DATABASE <database> OWNER
Possibility (b): Using the external tools that come with pg
postgres@dev> createuser --login
dev> createdb --owner=
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.