Syndicate content

Copying a PostgreSQL database from one computer to another

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

For PostgreSQL, there is a pair of functions named pg_dump and pg_restore.

In most cases that works great, however, some systems 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 cluster, 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, and 9.3 main. Get the version and name and concatenate with a slash in between.

Syndicate content

Diverse Realty

Diverse Realty Team

Want a New Home?
Want to Sell Your House?

Call Alex at
+1 (916)
220 6482

Alexis Wilke, Realtor
Salesperson
Lic. # 02024063

Cory Marcus, Broker
Lic. # 01079165

     

Terms of Site Index

Find the page/content you are looking for with our index.