Postgres:Database migration to a new server

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>