Home » Posts tagged "PostgreSQL"

SQL: Find & Replace text in all records

On type of query I always have to run when moving a website from its test environment to its live environment is to replace the test domain in all content pages with the live domain. With SQL this can be easily done with the REPLACE(text, search, substitution) function.

For example:

UPDATE some_table
SET some_field = REPLACE(some_field, 'test.example.com', 'example.com')

This updates all records in the table some_table and replaces test.example.com with example.com in the field some_field.

Knowing which SQL functions exist can really help a lot and save time to update data.

Using rsnapshot for daily PostgreSQL backups

Having done backups for MySQL using rsnapshot before I recently had to do the same thing for PostgreSQL. Turns out PostgreSQL has similar tools to do this and it’s actually quite easy to set it up.

First, assuming rsnapshot will run under the root user you’ll need to create a .pgpass file at /root/.pgpass that will hold the credentials that are required for setting up a connection with your database. The expected format is host:port:database:user:password, so for example:

localhost:*:mydbname:myusername:mypassword

With that in place create a shell script that will use this file and run the pg_dump program. Put it some place you can remember, e.g. /usr/local/bin/postgresql-backup.sh:

#!/bin/bash
export PGPASS=/root/.pgpass
pg_dump -w -h localhost -U myusername mydbname > postgresql-dump.sql
gzip postgresql-dump.sql

Again, just as with the using rsnapshot for daily MySQL backups article you need to configure rsnapshot.

Next is setting up /etc/rsnapshot.conf which is easy as well. At the end of this file you’ll find all the instructions of which directories to backup. Enter the next line. Do note that rsnapshot.conf uses a tab to separate values.

backup_script   /usr/local/bin/postgresql-backup.sh  postgresql/

To enable daily backups uncomment the next line:

#interval       hourly  6
interval        daily   7
#interval       weekly  4
#interval       monthly 3

To make rsnapshot run every day update /etc/cron.d/rsnapshot:

# 0 */4         * * *           root    /usr/bin/rsnapshot hourly
  30 3          * * *           root    /usr/bin/rsnapshot daily
# 0  3          * * 1           root    /usr/bin/rsnapshot weekly
# 30 2          1 * *           root    /usr/bin/rsnapshot monthly

Finally, try running rsnapshot. After it has run check your backup directory, which on Ubuntu 12.04 defaults to /.snapshots/. Be sure to mount /.snapshots/ on a separate drive. Because what use is it if your data drive contains your backup and decides to commit suicide?

$ sudo rsnapshot daily

Your PostgreSQL backup file should now be located at /.snapshots/daily.0/postgresql/postgresql-dump.sql. You can restore this file with the psql commandline utility.