Copying PostgreSQL database to another server

Question

I m looking to copy a production PostgreSQL database to a development server  What s the quickest  easiest way to go about doing this

User · Answer

pg dump the db name  gt  the backup sql   Then copy the backup to your development server  restore with   psql the new dev db  lt  the backup sql

User · Answer

I struggled quite a lot and eventually the method that allowed me to make it work with Rails 4 was   on your old server  sudo su - postgres pg dump -c --inserts old db name  gt  dump sql   I had to use the postgres linux user to create the dump  also i had to use -c to force the creation of the database on the new server  --inserts tells it to use the INSERT   syntax which otherwise would not work for me     then  on the new server  simpy   sudo su - postgres psql new database name  lt  dump sql   to transfer the dump sql file between server I simply used the  cat  to print the content and than  nano  to recreate it copypasting the content   Also  the ROLE i was using on the two database was different so i had to find-replace all the owner name in the dump

User · Answer

You don t need to create an intermediate file  You can do   pg dump -C -h localhost -U localuser dbname   psql -h remotehost -U remoteuser dbname   or  pg dump -C -h remotehost -U remoteuser dbname   psql -h localhost -U localuser dbname   using psql or pg dump to connect to a remote host   With a big database or a slow connection  dumping a file and transfering the file compressed may be faster    As Kornel said there is no need to dump to a intermediate file  if you want to work compressed you can use a compressed tunnel  pg dump -C dbname   bzip2   ssh  remoteuser remotehost  bunzip2   psql dbname    or  pg dump -C dbname   ssh -C remoteuser remotehost  psql dbname    but this solution also requires to get a session in both ends     Note  pg dump is for backing up and psql is for restoring  So  the first command in this answer is to copy from local to remote and the second one is from remote to local  More -  https   www postgresql org docs 9 6 app-pgdump html

User · Answer

Run this command with database name  you want to backup  to take dump of DB    pg dump -U  user-name   source db  -f  dumpfilename sql    eg  pg dump -U postgres mydbname -f mydbnamedump sql   Now scp this dump file to remote machine where you want to copy DB    eg  scp mydbnamedump sql user01 remotemachineip   some folder    On remote machine run following command in   some folder to restore the DB    psql -U  user-name  -d  desintation db -f  dumpfilename sql    eg  psql -U postgres -d mynewdb -f mydbnamedump sql

User · Answer

pg basebackup seems to be the better way of doing this now  especially for large databases   You can copy a database from a server with the same or older major version  Or more precisely      pg basebackup works with servers of the same or an older major version  down to 9 1  However  WAL streaming mode  -X stream  only works with server version 9 3 and later  and tar format mode  --format tar  of the current version only works with server version 9 5 or later    For that you need on the source server    listen addresses       to be able to connect from the target server  Make sure port 5432 is open for that matter  At least 1 available replication connection  max wal senders   1  -X fetch   2 for -X stream  the default in case of PostgreSQL 12   or more  wal level   replica or higher to be able to set max wal senders  gt  0  host  replication  postgres  DST IP 32  trust in pg hba conf  This grants access to the pg cluster to anyone from the DST IP machine  You might want to resort to a more secure option    Changes 1  2  3 require server restart  change 4 requires reload   On the target server     systemctl stop postgresql VERSION-NAME postgres  pg basebackup -h SRC IP -U postgres -D VERSION NAME --progress   systemctl start postgresql VERSION-NAME

User · Answer

Let me share a Linux shell script to copy your table data from one server to another PostgreSQL server   Reference taken from this blog   Linux Bash Shell Script for data migration between PostgreSQL Servers      bin bash psql       -X       -U user name       -h host name1       -d database name       -c    copy tbl Students to stdout        psql       -X       -U user name       -h host name2       -d database name       -c    copy tbl Students from stdin    I am just migrating the data  please create a blank table at your destination second database server   This is a utility script  Further  you can modify the script for generic use something like by adding parameters for host name  database name  table name and others

User · Answer

Use pg dump  and later psql or pg restore - depending whether you choose -Fp or -Fc options to pg dump   Example of usage   ssh production pg dump -C -Fp -f dump sql -U postgres some database name scp dump sql development  rm dump sql ssh development psql -U postgres -f dump sql

User · Answer

Accepted answer is correct  but if you want to avoid entering the password interactively  you can use this   PGPASSWORD   export db password   pg dump --create -h   export db host   -U   export db user     export db name     PGPASSWORD   import db password   psql -h   import db host   -U   import db user     import db name

User · Answer

If you are more comfortable with a GUI  you can use the pgAdmin software   Connect to your source and destination servers Right-click on the source db  gt  backup Right-click on the destination server  gt  create  gt  database  Use the same properties as the source db  you can see the properties of the source db by right-click  gt  properties  Right-click on the created db  gt  restore

User · Answer

Dump your database   pg dump database name name  gt  backup sql    Import your database back  psql db name  lt  backup sql

User · Answer

If you are looking to migrate between versions  eg you updated postgres and have 9 1 running on localhost 5432 and 9 3 running on localhost 5434  you can run   pg dumpall -p 5432 -U myuser91   psql -U myuser94 -d postgres -p 5434   Check out the migration docs

User · Answer

Here is an example using pg basebackup I chose to go this route because it backs up the entire database cluster  users  databases  etc    I m posting this as a solution on here because it details every step I had to take  feel free to add recommendations or improvements after reading other answers on here and doing some more research  For Postgres 12 and Ubuntu 18 04 I had to do these actions   On the server that is currently running the database  Update pg hba conf  for me located at  etc postgresql 12 main pg hba conf Add the following line  substitute 192 168 0 100 with the IP address of the server you want to copy the database to   host  replication  postgres  192 168 0 100 32  trust  Update postgresql conf  for me located at  etc postgresql 12 main postgresql conf  Add the following line  listen addresses        Restart postgres   sudo service postgresql restart   On the host you want to copy the database cluster to   sudo service postgresql stop sudo su root rm -rf  var lib postgresql 12 main   exit sudo -u postgres pg basebackup -h 192 168 0 101 -U postgres -D  var lib postgresql 12 main  sudo service postgresql start  Big picture - stop the service  delete everything in the data directory  mine is in  var lib postgreql 12   The permissions on this directory are drwx------ with user and group postgres  I could only do this as root  not even with sudo -u postgres  I m unsure why  Ensure you are doing this on the new server you want to copy the database to  You are deleting the entire database cluster  Make sure to change the IP address from 192 168 0 101 to the IP address you are copying the database from  Copy the data from the original server with pg basebackup  Start the service  Update pg hba conf and postgresql conf to match the original server configuration - before you made any changes adding the replication line and the listen addresses line  in my care I had to add the ability to log-in locally via md5 to pg hba conf   Note there are considerations for max wal senders and wal level that can be found in the documentation  I did not have to do anything with this

[database] Copying PostgreSQL database to another server

Examples related to database

Examples related to postgresql