When moving a database from one computer to another, you've got to copy all the data.

For that purpose PostgreSQL offers a pair of functions named pg_dump and pg_restore.

In most cases that works great, however, some applications may create things such as additional functions that will NOT make it across. I do not know how to get that to work (and maybe it works in newer version of PostgreSQL?)

In any event, in most cases the following commands are enough (I used them with Drupal websites and since they stopped using some of the definitions they have in the system.install file used on initialization, it works as is.)

The DUMP command goes like this:

pg_dump --format=c --file=db.pgsql --host=localhost --username=login --password db

If you do not have a password in your database, then do not use the --username and --password. Also the --host is not required if it is localhost (the default). So you may be able to simplify the command line to:

pg_dump --format=c --file=db.pgsql db

Then copy the file from your current server to the new server, maybe something like that:

scp db.pgsql new.server.com:.

Now, on your new server, run the restore with something like this:

First create the database (you can also ask the pg_restore command to do that, but I prefer to do it myself to make sure I don't get errors):

user$ psql template1
pgsql> CREATE DATABASE db;
pgsql> \q

Then run the restore command:

pg_restore --dbname=db --host=localhost --username=drupal --password db.pgsql

Note that --dbname is the name of the database in PostgreSQL. By creating the database manually you can also change its name at that time.

The name at the end of the command line (db.pgsql) is the filename that you transferred on your new server.

In case you are running multiple clusters, the pg_restore scheme will select the specific pg_restore of the first running cluster (at least in my experience.) The obvious result is: you are likely to get the wrong one. To force a cluster run the following two commands:

pg_lsclusters
pg_restore --cluster 9.3/main --dbanem=db --host=localhost --username=drupal --password db.pgsql

The pg_lsclusters will give you a list of your custers, such as:

• 8.3 main
• 9.1 main
• 9.3 main

Get the version and name and concatenate them with a slash in between.

Transform to SQL Commands

The binary format of the output is better because it is more likely to safe all the necessary data than the SQL text format. One issue, as a result, you just can't read it as a normal human being.

You can decompress the data and generate a plain text .sql file using the pg_restore command like so:

pg_restore -f db.pgsql db.sql

Now the db.sql file is plain text and you can read it and see if everything is as expected.