PostgreSQL (IX)
Configuration
/etc/postgresql/9.1/main/pg_hba.conf:
-------------------------------------
...
# "local" is for Unix domain socket connections only
local all all md5
_$: service postgresql restart
Encoding
_$: su - postgres
(postgres)_$: psql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
postgres=# \q
If the encoding is not alright, we can recreate the cluster. We will probably lose the data in the databases, so be careful.
(postgres)_$: pg_dropcluster --stop 9.1 main
(postgres)_$: pg_createcluster --start 9.1 main
Creating new cluster (configuration: /etc/postgresql/9.1/main, data: /var/lib/postgresql/9.1/main)...
Moving configuration file /var/lib/postgresql/9.1/main/postgresql.conf to /etc/postgresql/9.1/main...
Moving configuration file /var/lib/postgresql/9.1/main/pg_hba.conf to /etc/postgresql/9.1/main...
Moving configuration file /var/lib/postgresql/9.1/main/pg_ident.conf to /etc/postgresql/9.1/main...
Configuring postgresql.conf to use port 5432...
Load data
_$: psql -U <user> -d <database> -f /var/webapp/data/sql/demo-data.sql
_$: psql -U <user> -d <database> -c "SELECT count(*) FROM ips;"
Create backups
_$: mkdir /var/postgres-backups
_$: chmod 0770 /var/postgres-backups
_$: chown postgres:postgres /var/postgres-backups
_$: mkdir -p /var/postgres-backups/wals/archive
_$: mkdir -p /var/postgres-backups/wals/current
_$: chmod -R 0770 /var/postgres-backups/wals/
_$: chown -R postgres:postgres /var/postgres-backups/wals
/var/lib/postgresql/9.1/archive.sh:
-----------------------------------
# Use: archive_command='archive.sh %p %f'
PATH=$1
FILE=$2
PG_STATUS="/tmp/pg-status"
echo "user: $(/usr/bin/whoami)" > $PG_STATUS
echo "path: $PATH" >> $PG_STATUS
echo "file: $FILE" >> $PG_STATUS
# Copy to archive
/bin/cp /var/lib/postgresql/9.1/main/$PATH /var/postgres-backups/wals/archive/$FILE
echo "($?) copied to archive" >> $PG_STATUS
/var/lib/postgresql/9.1/backup.sh:
----------------------------------
# Use: ./backup.sh <postgres-user> <backup-label>
DATA_DIR="/var/lib/postgresql/9.1"
BACKUP_DIR="/var/postgres-backups"
if [ $# -ne 2 ]
then
echo "Use: ${0##*/} <postgres-user> <backup-label>"
exit 1
fi
POSTGRES_USER=$1
BACKUP_LABEL=$2
echo "Making backup directories"
mkdir -p $BACKUP_DIR
chmod 0770 $BACKUP_DIR
chown postgres:postgres $BACKUP_DIR
mkdir -p $BACKUP_DIR/wals/archive
mkdir -p $BACKUP_DIR/wals/current
chmod 0770 $BACKUP_DIR/wals
chmod 0770 $BACKUP_DIR/wals/archive
chmod 0770 $BACKUP_DIR/wals/current
chown postgres:postgres $BACKUP_DIR/wals
chown postgres:postgres $BACKUP_DIR/wals/archive
chown postgres:postgres $BACKUP_DIR/wals/current
echo "Starting backup"
psql -U $POSTGRES_USER -c "SELECT pg_start_backup('$BACKUP_LABEL')"
cd $DATA_DIR
tar -cvf main.tar --exclude=*pg_xlog* main
mv main.tar $BACKUP_DIR
echo "Stopping backup"
psql -U $POSTGRES_USER -c "SELECT pg_stop_backup(), current_timestamp"
_$: chown postgres:postgres /var/lib/postgresql/9.1/*.sh
_$: chmod u+x /var/lib/postgresql/9.1/*.sh
/etc/postgresql/9.1/main/postgresql.conf:
-----------------------------------------
...
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
...
# - Settings -
...
wal_level = archive
...
# - Archiving -
...
archive_mode = on
archive_command = '/var/lib/postgresql/9.1/archive.sh "%p" "%f"'
_$: service postgresql restart
Automate backups
/root/cron/database-backup.sh:
------------------------------
user="webapp"
database="webapp"
/var/lib/postgresql/9.1/backup.sh ${user} "${database} - $(date)"
/root/cron/database-clean.sh:
-----------------------------
BACKUPPATH="/var/postgres-backups/wals/archive"
delete_older_than() {
# Arguments:
# $1: path
# $2: age (in days)
if [ $# -ne 2 ]
then
printf "Not enough arguments\n"
else
printf "Deleting all files older than $2 days in $1\n"
find $1 -type f -mtime +$2 -exec echo {} \; | sort
find $1 -type f -mtime +$2 -exec rm {} \;
printf "\n"
fi
}
# Delete old files
delete_older_than ${BACKUPPATH} 60
We can add to the crontab file the following entries to automate the backup process:
### PostgreSQL
0 9 * * 1 /root/cron/database-backup.sh
0 10 * * 1 /root/cron/database-clean.sh
Create a .pgpass file for password storing
_$: touch /root/.pgpass
/root/.pgpass:
--------------
#Format host:port:user:database:password
localhost:5432:txbase:txbase:...
_$: chmod 0600 /root/.pgpass
Using a socket in a non-default directory
Depending on the installation, the socket directory might be in /tmp or /var/run/postgresql. In most operating systems, the socket is created in /tmp but in Debian/Ubuntu it is expected to be in /var/run/postgresql, so change it accordingly.
If the socket is not where PostgreSQL expects it to be, we will be warned with the following message:
Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
You can change the socket’s directory in the CONNECTIONS AND AUTHENTICATION section:
/etc/postgresql/9.1/main/postgresql.conf:
-----------------------------------------
...
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directory = '/var/run/postgresql' # (change requires restart)
...
And you can check where it is created with netstat:
_$: service postgresql restart
_$: netstat -lx
Active UNIX domain sockets (only servers)
Proto RefCnt Flags Type State I-Node Path
...
unix 2 [ ACC ] STREAM LISTENING 27575 /var/run/postgresql/.s.PGSQL.5432
...