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>

Database Migration from Bowie to Hopper

Last updated on August 2022

On Bowie there are some databases that are only needed for research and faculty. Some of the faculty’s databases contain important information that has to be secured and accessed by the specific users. To ensure that faculty’s databases and data are secure, system administrators decided to move those databases to Hopper server where research databases on PostgreSQL won’t be mixed up with students’ assignments and practice databases. The following is the list of databases that are used for research or contain valuable information and they were moved to Hopper for security;

• AB-hopper
• AB_ts-hopper
• FSE-hopper
• FSE_test-hopper
• LearnGIS-hopper
• WHD-hopper
• b-and-t-g-hopper
• bccd-hopper
• bidforme
• datascience-hopper

dc-tests-hopper emergency-hopper energy epic_db field_science field_science-hopper field_science_testing-hopper field_science_v2 fieldsci_db fs_test-hopper fs_test_cjearley13-hopper fs_test_craig hospital_jpa_db ifs2018_db-hopper inventory mydat pgsql-hopper phi_account phi_account_test phi_account_test_db phi_test_2_db phi_test_db seniorsem-hopper template0 template1 trac_debian-cluster-hopper transport_test-hopper tvbase voting_data-hopper weather weatherduck-hopper x-hopper

The following processes were used for moving or migrating the above databases to Hopper; Firstly, the newest version of PostgreSQL 14.3 was installed on Hopper. Then, the PostGIS extension was created to facilitate data that are geographical and location sensitive. Using psql database_name > dumped_database_name.sql command each of the above databases were dumped into separate .sql files. Then by using the command scp dumped_database_name.sql username@cluster.earlham.edu :/location/to/store the dumped files were transferred to Hopper. Then by using the command sudo -u postgres psql < dumped_database_name.sql the dumped files were migrated to PostgreSQL on Hopper. Again, the command select * from fieldday_trip was used to ensure the databases are restored properly.