Re: moving postgresql files to seperate mount
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./ 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
Re: moving postgresql files to seperate mount
Hi all, thanks for the replies I will try to keep them in mind while I try to move my databases :) Regards Am 01.06.2016 um 17:22 schrieb trondd: On Wed, June 1, 2016 3:45 am, Markus Rosjat wrote: Hi there, just need some kind of acknowledgement for my workflow :) a naive approach would be: - extend the virtual disk - create a partition /var/postgresql (thats the folder under var right now) - move the files to the new partition - hope it works :-P So hope someone with experience in such scenario can give me a hint or too You're working with virtual machines? What I do is put /var/postgresql on it's own virtual disk. No growing the disk later then tacking on partitions as the data grows. If I need more space, add a new disk, copy the data, unmount the old, mount the new. Benefits I see of this approach: No possible problems resulting from changing the "physical" disk size. No leftover partitions in the middle of the disk. After a migration, the old disk is still there for an easy rollback. In the event of a problem with the server OS, or for testing, or for an easy upgrade via re-install, you can detach the postgres data disk (or copy it) and attach it to a new server OS install. Tim. -- Markus Rosjatfon: +49 351 8107223mail: ros...@ghweb.de G+H Webservice GbR Gorzolla, Herrmann Königsbrücker Str. 70, 01099 Dresden http://www.ghweb.de fon: +49 351 8107220 fax: +49 351 8107227 Bitte prüfen Sie, ob diese Mail wirklich ausgedruckt werden muss! Before you print it, think about your responsibility and commitment to the ENVIRONMENT
Re: moving postgresql files to seperate mount
On Wed, June 1, 2016 3:45 am, Markus Rosjat wrote: > Hi there, > > just need some kind of acknowledgement for my workflow :) > > a naive approach would be: > > - extend the virtual disk > - create a partition /var/postgresql (thats the folder under var right > now) > - move the files to the new partition > - hope it works :-P > > So hope someone with experience in such scenario can give me a hint or too > > You're working with virtual machines? What I do is put /var/postgresql on it's own virtual disk. No growing the disk later then tacking on partitions as the data grows. If I need more space, add a new disk, copy the data, unmount the old, mount the new. Benefits I see of this approach: No possible problems resulting from changing the "physical" disk size. No leftover partitions in the middle of the disk. After a migration, the old disk is still there for an easy rollback. In the event of a problem with the server OS, or for testing, or for an easy upgrade via re-install, you can detach the postgres data disk (or copy it) and attach it to a new server OS install. Tim.
Re: moving postgresql files to seperate mount
Den 2016-06-01 kl. 09:45, skrev Markus Rosjat: Hi there, just need some kind of acknowledgement for my workflow :) a naive approach would be: - extend the virtual disk - create a partition /var/postgresql (thats the folder under var right now) - move the files to the new partition - hope it works :-P So hope someone with experience in such scenario can give me a hint or too Thanks and regards Hi, Normally it works :-) (don't forget to make a pg_dump of all databases). I don't use extend, I just adds a new disk and mounts it temporary some where and copies the data and then mounts it on the right folder. As an alternative you can mount the new file system wherever you like and point to in in the config file but I don't like that. Non standard places easy creates mistakes. \\at
moving postgresql files to seperate mount
Hi there, just need some kind of acknowledgement for my workflow :) a naive approach would be: - extend the virtual disk - create a partition /var/postgresql (thats the folder under var right now) - move the files to the new partition - hope it works :-P So hope someone with experience in such scenario can give me a hint or too Thanks and regards -- Markus Rosjatfon: +49 351 8107223mail: ros...@ghweb.de G+H Webservice GbR Gorzolla, Herrmann Königsbrücker Str. 70, 01099 Dresden http://www.ghweb.de fon: +49 351 8107220 fax: +49 351 8107227 Bitte prüfen Sie, ob diese Mail wirklich ausgedruckt werden muss! Before you print it, think about your responsibility and commitment to the ENVIRONMENT
