Tag: postgres
Complex Restore of Postgres data where multiples rows are deleted from a single row.
Complex Restore of Postgres data where multiples rows are deleted from a single row.
We ran into an issue with a client database where an application deleted upwards of 360 K records. In this process we found we needed to restore a database from several hours ago but we were unable to do a full system restore, so here is the recepie for how to resolve
- restore a database gziped file to a ‘sandbox’ database
#gunzip -c database_back.1014|psql -Uuser database_restore_20150909 - alter the public schema to rename it
#echo ‘alter schema public rename to ‘restore_09122015’|psql -Uuser database_restore_20150909 - dump the database and load to the live db in the new schema
#pg_dump -Uuser database _restore_20150909|psql -Uuser livedb - create insert statements which will insert all missing records from the new schema into the live (public) schema
#awk ‘/tbl/{gsub(“tbl”,””); print “insert into tbl”$1″ select r.* from restore_09122015.tbl”$1″ r left join tbl”$1” n using(“$1″id) where n is null; “}’ tbllist.save | psql –U user livedb.
Note that this relies on your database being in the same consistent naming format tblshortname.shortnameid.
Two step recipe – upgrading from Postgres 8.4 to 9.3 and then implementing 9.3 hot_standby replication
Two step recipe – upgrading from Postgres 8.4 to 9.3 and then implementing 9.3 hot_standby replication
Upgrade and existing postgresql database from Postgres 8.4 to 9.3 and then implementing a 9.3 hot_standby replication server so all backups and slow select queries can run from it.
The setup: two servers, the current primary database server (will continue to be the primary database server when using 9.3, but we will call it the master in the replication)
First get and install postgres 9.3 using the postgres apt repositories
master and standby> vi /etc/apt/sources.list - deb http://apt.postgresql.org/pub/repos/apt/ UNAME-pgdg main #UNAME EXAMPLES: precise, squeeze, etch, etc master and standby> apt-get update master> apt-get install postgresql-9.3 postgresql-client-9.3 nfs-kernel-server nfs-client standby> apt-get install postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 nfs-kernel-server nfs-client
Next create some shared directorys via nfs for file based archiving
standby> mkdir -p /data/dbsync/primaryarchive master> mkdir -p /data/dbsync-archiveto master> vi /etc/exports - /var/lib/postgresql/9.3/main 192.168.*.*(ro,sync,no_subtree_check,no_root_squash) standby> vi /etc/exports - /data/dbsync/primaryarchive 192.168.*.*(rw,sync,no_subtree_che master> vi /etc/fstab - SECONDARYSERVERIP:/data/dbsync/primaryarchive /data/dbsync-archiveto nfs ro 0 1 standby>mkdir -p /mnt/livedb standby> mount PRIMARYSERVERIP:/var/lib/postgresql/8.4/main/ /mnt/livedb master> mount /data/dbsync-archiveto
Now, configure postgres on the master to allow replication and restart, put it on port 5433 so there are no conflictw with 8.4
master> vi /etc/postgresql/9.3/main/pg_hba.conf - host replication all SECONDARYSERVERIP trust master> vi /etc/postgresql/9.3/main/postgresql.conf - wal_level=hot_standby - archive_mode = on - port = 5433 - archive_command = 'test -f /data/dbsync-archiveto/archiveable && cp %p /data/dbsync-archiveto/%f' master> /etc/init.d/postgresql restart
Configure postgres on the standby to allow it to run as a hot_standby
standby> vi /etc/postgresql/9.3/main/postgresql.conf -restore_command = a018/usr/lib/postgresql/9.3/bin/pg_standby -d -t /tmp/pgsql.trigger.5432 /data/dbsync/primaryarchive %f %p %r 2>>/var/log/postgresql/standby.log -recovery_end_command = a018rm -f /tmp/pgsql.trigger.5432 - wal_level=hot_standby - hot_standby = on standby> /etc/init.d/postgresql stop
Now lets get a base backup on the standby
standby> mv /var/lib/postgresql/9.3/main /var/lib/postgresql/9.3/main.old standby>cd /var/lib/postgres/9.3; mv main main.old; standby> pg_basebackup -D main -R -h192.168.120.201 -p5433 -x -Upostgres standby> chown postgres.postgres main/ -R standby> /etc/init.d/postgres start
Thats it!!, you should not have a working replication server
primary> create table tmp as select now(); secondary> select * from tmp;
#check the progress several ways. postregres log, which files and recovery are running and by being able to connect and see updates from the master, on the secondary
standby> tail /var/log/postgresql/postgresql-9.3-main.log
standby> grep 'database system is ready to accept read only connections'
standby> ps ax|grep post
- postgres: wal receiver process streaming 3/43000000
master> psql -Upostgres -c 'select pg_switch_xlog()'
and the log file would switch in the recovery file
standby> ps ax|grep post
- postgres: startup process recovering 000000010000000300000037
That was all to make sure that the replication is working on 9.3, now that I am comfortable with it working, I am going to turn off the replication, copy the data from 8.4 to 9.3 and recreate the replication
First lets stop the postgresql daemon on the standby server so the VERY heavy load from copying the db from 8.4 to 9.3 is not duplication
standby> /etc/init.d/postgresql stop
Next, copy the database from 8.4 to 9.3, I have heard there may be some problems for conversion of some objects between 8.4 and 9.3 but not for me, this went great.
master> pg_dump -C -Upostgres mydatabase| psql -Upostgres -p5433
Once that is successful, lets switch ports on the 9.3 and 8.4 servers so 9.3 can take over
master>vi /etc/postgresql/9.3/main/postgresql.conf - port = 5432 master>vi /etc/postgresql/8.4/main/postgresql.conf - port = 5433 master> /etc/init.d/postgres reload Last step, get a base backup and start again. standby> mv /var/lib/postgresql/9.3/main /var/lib/postgresql/9.3/main.old standby>cd /var/lib/postgres/9.3; mv main main.old; standby> pg_basebackup -D main -R -h192.168.120.201 -x -Upostgres standby> chown postgres.postgres main/ -R standby> /etc/init.d/postgres start standby> rm /var/lib/postgres/9.3/main.old* -rf Now..... to figure out what to do with the archivedir method we are currently using..... It seems that it is just building up when do we use it?
PHP to reset all primary key sequences in your postgresql database
PHP to reset all primary key sequences in your postgresql database
Use the following php code t reset all of the primary key sequences with the max(id) currently in the db.
We use wrapper functions db_query (which returns an array from the db when a select statement is run) and db_exec() which runs an update or insert command against the db.
[code language=”php”]$sql = "SELECT t.relname as related_table,
a.attname as related_column,
s.relname as sequence_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid
JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid
JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
JOIN pg_namespace n ON n.oid = s.relnamespace
WHERE s.relkind = ‘S’
AND n.nspname = ‘public’";
$qry = db_query($sql);
foreach($qry as $row)
{
$outsql = "select setval(‘$row[sequence_name]’,(select max($row[related_column]) from $row[related_table]))";
db_exec($outsql);
}[/code]
Setting up postgres warm standby
Setting up postgres warm standby
Mainly just notes, I haven’t gone through this in too much detail yet…. -MB
Setup two postgres servers with the same version, both with the same data directory layout.
AS ROOT
primary> apt-get install postgresql-server nfs-kernel-server nfs-client
secondary> apt-get install postgresql-server nfs-kernel-server nfs-client postgresql-contrib-8.4
secondary> mkdir -p /data/dbsync/primaryarchive
primary> mkdir -p /data/dbsync-archiveto
primary> vi /etc/exports
#to temporarily allow access to the base data for getting a base
/var/lib/postgresql/8.4/main 192.168.*.*(ro,sync,no_subtree_check,no_root_squash)
secondary> vi /etc/exports
#to provide a location on the secondary, that the primary can write WAL logs to
/data/dbsync/primaryarchive 192.168.*.*(rw,sync,no_subtree_che
primary> vi /etc/fstab
SECONDARYSERVERIP:/data/dbsync/primaryarchive /data/dbsync-archiveto nfs ro 0 1
primary> mount /data/dbsync-archiveto
primary> vi /etc/postgresql/8.4/main/postgresql.conf
#to turn on archiving from primary to the secondary
-archive_mode = on
-archive_command ‘cp %p /data/dbsync-archiveto/%f’
secondary>mkdir -p /mnt/livedb
secondary> mount PRIMARYSERVERIP:/var/lib/postgresql/8.4/main/ /mnt/livedb
At this point we should have a postgres db running on both primary and secondary in /var/lib/postgresql/8.4/main
we should have a mount on the primary pointing to the secondary and WAL logs being writted to the secondary. If the mount to the secondary fails the WAL Logs would build up on the primary in /var/lib/postgresql/8.4/main/pg_xlog.
we should have a mount on the secondary pointing to the base install of the primary database so we can copy the base
secondary> /etc/init.d/postgresql stop
secondary> rsync -Cqtar –delete /mnt/livedb/* /var/lib/postgresql/8.4/main/.
secondary> vi /var/lib/postgresql/8.4/main/recovery.conf
While that is going, setup the configuration on secondary to be monitoring the archive directory on primary and processing the archive entries.
secondary> vi /etc/postgresql/8.4/main/postgresql.conf
restore_command = ‘/usr/lib/postgresql/8.4/bin/pg_standby -d -t /tmp/pgsql.trigger.5432 /data/dbsync/primaryarchive %f %p %r 2>>/var/log/postgresql/standby.log’
recovery_end_command = ‘rm -f /tmp/pgsql.trigger.5432’
———————
(the above is ideal, but sometimes NFS mount issues cause problems, here are some shortcuts I have used….)
– use the mount from primary to secondary to do the initial base transfer.
- On the secondary stop postgres (/etc/init.d/postgresql stop)
- mv /var/lib/postgresql/8.4/main to data/dbsync/primaryarchive ( /data/dbsync/primaryarchive/main)
- from the primary start a backup and sync to the secondary
- primary> psql -Upostgres -c “select pg_start_backup(‘backmeup’)” && time rsync –delete -Ctar /var/lib/postgresql/8.4/main/* /data/dbsync-archiveto/main/. –progress –exclude=’*pg_xlog/*’ && psql -Upostgres -c “select pg_stop_backup()”
- mv main back to the running location (mv /data/dbsync/primary/main /var/lib/postgresql/8.4/.)
- make sure all files are property owned by postgres.postgres (chown postgres.postgres -R /var/lib/postgresql/8.4/main)
- vi /var/lib/postgresql/8.4/main/recovery.conf
- restore_command = ‘/usr/lib/postgresql/8.4/bin/pg_standby -d -t /tmp/pgsql.trigger.5432 /data/dbsync/primaryarchive %f %p %r 2>>/var/log/postgresql/standby.log’
recovery_end_command = ‘rm -f /tmp/pgsql.trigger.5432’ - /etc/init.d/postgresql start