Postgres:Database migration to a new server

From Earlham CS Department
Jump to navigation Jump to search

Install and configure the new Postgres server:

  • UPGRADE THE SYSTEM
make sure apt's source.list is up to date
run update
sudo apt-get update
run upgrade
sudo apt-get upgrade
finish the upgrade
sudo apt-get dist-upgrade
this requers reboot
sudo reboot
useful commands to check the update
lsb_release -a
uname -mrs
cat /etc/issue
  • INSTALL AND CONFIGURE POSTGRES
install postgres package
sudo apt-get install <postgresql-version>
copy over and modify where needed postgres configs from old server
postgresql.conf and pg_hba.conf are the ones that matter
make sure to replace the old hostname with the new one where needed in the configs
when copying over make sure the permissions and ownerships are preserved
restart postgres server
sudo /etc/init.d/postgresql restart
make sure you can login as postgres user and watch the configs for bugs
  • FIREWALL
allow all to postgres sockets
sudo /sbin/iptables -A INPUT -i <interface> -s <IP-range> -p tcp --sport 1024:65535 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
  • SEMAPHORES: if more than one database is running on the server, the semaphore issues may occur
in this case the shmmax and shmall need to be modified
useful commands:
sysctl -A | grep kernel.sem
ipcs -lm
see loader.conf, shmmax and shmall files
  • TEST FOR LOCAL AND REMOTE CONNECTIONS AND WATH LOGS
if versions of postgres vary between old and new server check for compatibility issues that may occur during the dump


Postgres Database Migration Using Compressed File

  • DNS
comment out old server entry as a postgres host
  • ON OLD SERVER
become postgres
sudo su - postgres
dump all databases
pg_dumpall -f psq_dump.sql
compress the dump
gzip psq_dump.sql
move the dump file to the new server
scp psq_dump.sql <new_server>:
check md5sum
  • ON NEW SERVER
decompress the dump file
gunzip psq_dump.sql.gz psq_dump.sql
put psq_dump.sql to postgres directory
make sure the dump file has postgres user and group ownership
chown postgres:postgres psq_dump.sql
become postrgrees
sudo su - postgres
drop all existing databases and rules by running drop.sql script or doing so manually
to run the script within postgres
psql -U postgres
\i drop.sql
manually drop the objects that failed to drop (or solve this)
modify the dump file if needed to adjust for postgres version upgrade
restore the dump
psql -f psq_dump.sql postgres > output.dat
check for errors and make sure all data made it
  • CREATE NEW DNS ENTRY AND SHUT DOWN DATABASE ON OLD SERVER

Direct Postgres Database Migration

  • ON NEW SERVER
create new database
createdb -O <owner> <new database name>
  • ON OLD SERVER
become posgres
sudo su - postgres
dump databases and transfer from local host to new host
pg_dump -C <current database name> -h 127.0.0.1 | psql -h <new host> <new database name>