Re: moving postgresql files to seperate mount

[Craig Skinner]

Hi Markus,

On 2016-06-01 Wed 09:45 AM |, Markus Rosjat wrote:
>  - create a partition /var/postgresql (thats the folder under var right now)
>  - move the files to the new partition

dump & restore is best:
http://www.openbsd.org/faq/faq10.html#DupFS

I've a nightly script run as _postgresql, which does:
1) a pg_dumpall in a /var/postgresql/backups/tmp.XXXXXXXX/ dir
2) gzips the dump
3) diffs the compressed dump with the prior one in /var/postgresql/backups/
4) if the diff fails, mv the compressed dump up to the backups/ dir
Then does the same for each table.
Clean up the tmp dir when done.

Afterwards, I've another script which umounts /var/postgresql & dump(8)
the whole lot, mounts /var/postgresql & starts the daemon again.

Next, the file system dump is compressed, encrypted & a copy SSH rdisted
off site, by the operator user. None of this runs as root.


Works for me, public domain, do with it as you like:


#!/bin/ksh
#
#       $Id: _var_postgresql.ksh,v 1.7 2015/07/10 20:28:23 craig Exp $
#

[[ ${LOGNAME} == '_postgresql' ]] || exit

pg_dump_cleanup()
{
        rm -f * # should find(1) files in ${PWD}
        cd ${OLDPWD}
        rmdir ${OLDPWD}
        exit $1
}


cd /var/postgresql || return
[[ -d backups ]] ||
{
        mkdir backups || return
}
cd backups || return
cd $(mktemp -d -p $PWD) || return

PGOPTIONS='--username=postgres --no-password'
# cmp -s would be better than diff(1):
alias diff_pg_dump='diff ${OLDPWD}/${_pg_dump} ${_pg_dump} > /dev/null 2>&1 || 
mv -f ${_pg_dump} ${OLDPWD}'

_pg_dump='PostgreSQL-database-cluster-dump.sql'
pg_dumpall ${PGOPTIONS} --clean --file=${_pg_dump} || pg_dump_cleanup $?
gzip -n ${_pg_dump} || pg_dump_cleanup $?
_pg_dump="${_pg_dump}.gz"
$(diff_pg_dump)

_pg_dump='PostgreSQL-database-globals-dump.sql'
pg_dumpall --globals-only ${PGOPTIONS} --clean --file=${_pg_dump} || 
pg_dump_cleanup $?
$(diff_pg_dump)

_pg_tables="SELECT DATNAME FROM pg_catalog.pg_database WHERE DATNAME NOT IN 
('postgres', 'template0', 'template1');"
_pg_tables=$(psql ${PGOPTIONS} --tuples-only --command="${_pg_tables}")
PGOPTIONS="${PGOPTIONS} --clean --create"
for _pg_table in ${_pg_tables}
do
        _pg_dump="${_pg_table}-schema-dump.sql"
        pg_dump ${PGOPTIONS} --schema-only --file=${_pg_dump} ${_pg_table}
        $(diff_pg_dump)

        _pg_dump="${_pg_table}-full-dump.sql"
        pg_dump ${PGOPTIONS} --file=${_pg_dump} ${_pg_table}
        gzip -n ${_pg_dump}
        _pg_dump="${_pg_dump}.gz"
        $(diff_pg_dump)
done

pg_dump_cleanup