Attached is a patch for /etc/init.d/postgresql and /etc/conf.d/postgresql
A lbu commit of the /var/lib/postgresql/x.x/data directory takes a very
long time if the database is running.
apk-tools 2.0 includes the ability (/etc/lbu/pre-package.d) to make a
pg_dumpall of the databases, and then commit the archive. This is much
faster.
There has not been a way to automatically restore the backup on reboot.
This patch attempts to address that issue
Limitations: The archive is assumed to be created with
pg_dumpall -U postgres | gzip -c >/some/path/here.gz
* bzip2 or plain dumps are not supported
You must do a lbu_include /some/path/here.gz
and also edit /etc/conf.d/postgresql to define LBU_BACKUP=/some/path/here.gz
postgresql stores the pg_hba.conf (access controls) and postgresql.conf
in $PGDATA; so if you have password protection on the postmaster account
(a good idea); that gets lost.
This script addresses that by moving the files in $PGDATA out of the
way, restoring the database, and then moving them back.
This way you can do a lbu include
/var/lib/postgresql/8.4/data/pg_hba.conf and get the access controls on
the restored databases.
On Thu, May 13, 2010 at 8:10 AM, Nathan Angelacos <nangel@nothome.org> wrote:
>> Attached is a patch for /etc/init.d/postgresql and /etc/conf.d/postgresql
Thanks!
> A lbu commit of the /var/lib/postgresql/x.x/data directory takes a very long> time if the database is running.
And is not safe when you upgrade.
> apk-tools 2.0 includes the ability (/etc/lbu/pre-package.d) to make a> pg_dumpall of the databases, and then commit the archive. This is much> faster.
(lbu is provided by alpine-conf, not apk-tools)
> There has not been a way to automatically restore the backup on reboot.
we added pg-restore initscript not too long ago and AUTO_SETUP to auto
init and empty database.
> This patch attempts to address that issue>> Limitations: The archive is assumed to be created with> pg_dumpall -U postgres | gzip -c >/some/path/here.gz>> * bzip2 or plain dumps are not supported>> You must do a lbu_include /some/path/here.gz> and also edit /etc/conf.d/postgresql to define LBU_BACKUP=/some/path/here.gz
I'd prefer using uncompressed dump since lbu will compress it again.
> postgresql stores the pg_hba.conf (access controls) and postgresql.conf in> $PGDATA; so if you have password protection on the postmaster account (a> good idea); that gets lost.>> This script addresses that by moving the files in $PGDATA out of the way,> restoring the database, and then moving them back.>> This way you can do a lbu include /var/lib/postgresql/8.4/data/pg_hba.conf> and get the access controls on the restored databases.
This is a feature we dont have with pg-restore.
How do you create the dump if postgres user is password protected?
I was initially thinking it was a good idea to separate the restoring
to other service to make it easy to enable or disable it (rc-update
add pg-restore) but with the pg_hba.conf feature its probably easier
to embed it in same service.
I also think that we want provide a way to do the dump from the init.d
script too, so you from /etc/lbu/pre-package.d/ can do:
'/etc/init.d/postgresql dump' or something. Then you dont need to
worry about .gz .bz2 or whatever since the script does that for you.
You could still configure it via /etc/conf.d/ tough.
Please have a look at the pg-restore from git master. We need to either:
* move the pg_hba.conf protection feature to pg-restore
* replace the pg-restore with your patch (not keep both in any case)
* assume that you have postgres access when connecting from localhost
and keep pg-restore as-is
* add a password variable that is used when connecting to db to do the
dump/restore via pg-restore script.
I wonder if we can assume that if you run as postgres user and connect
via local unix socket, then you have full access to database. That is,
if you su - postgres, then you can psql and pg_dumpall, (or run as
root). But if you run as normal user, lets say ncopa, and does a psql
-U postgres, then you get reject or prompted for password.
The reasoning is that since you are running as root you do have (raw)
access to the database anyway so its no big point try protect the db
from local root user. It does make sense to protect it from other
local users and definitively from remote connections. (i though tcp
connections are off by default?)
So technically, beeing able to do dump/restore as local root user
doesnt need be a bad idea.
I'm not too happy about storing the password as cleartext though.
--
Natanael Copa
---
Unsubscribe: alpine-devel+unsubscribe@lists.alpinelinux.org
Help: alpine-devel+help@lists.alpinelinux.org
---
On 05/13/10 00:10, Natanael Copa wrote:
<snip>
>> apk-tools 2.0 includes the ability (/etc/lbu/pre-package.d) to make a>> pg_dumpall of the databases, and then commit the archive. This is much>> faster.>> (lbu is provided by alpine-conf, not apk-tools)>
#apk info -W /sbin/lbu
/sbin/lbu is owned by alpine-conf-2.3-r0
Whoops. I stand corrected.
>> There has not been a way to automatically restore the backup on reboot.
<snip>
>> This way you can do a lbu include /var/lib/postgresql/8.4/data/pg_hba.conf>> and get the access controls on the restored databases.>> This is a feature we dont have with pg-restore.>> How do you create the dump if postgres user is password protected?
I was expecting that would be left as a job for the local admin.
>> I was initially thinking it was a good idea to separate the restoring> to other service to make it easy to enable or disable it (rc-update> add pg-restore) but with the pg_hba.conf feature its probably easier> to embed it in same service.>> I also think that we want provide a way to do the dump from the init.d> script too, so you from /etc/lbu/pre-package.d/ can do:> '/etc/init.d/postgresql dump' or something. Then you dont need to> worry about .gz .bz2 or whatever since the script does that for you.> You could still configure it via /etc/conf.d/ tough.>> Please have a look at the pg-restore from git master. We need to either:>> * move the pg_hba.conf protection feature to pg-restore> * replace the pg-restore with your patch (not keep both in any case)> * assume that you have postgres access when connecting from localhost> and keep pg-restore as-is> * add a password variable that is used when connecting to db to do the> dump/restore via pg-restore script.
I looked at pg-restore, and understand what you are attempting; but it
won't work currently:
* $PGDATA/p*.conf required modification to use postgresql with anything
other than local "trust" access. That means we must either
A) manage them as part of pg-restore
B) move them out of the way during a postgresql setup
In either case we must handle the conf files.
* The standard way to back up a file in the apkovl is lbu include; doing
so means $PGDATA will exist when postgresql is started, so the autosetup
will fail through, but there's no data, and postgresql will fail to
start. pg-restore depends on postgresql, and will then fail - there's
no way to restart postgresql from a restore. Options:
A) pg-restore manages those files itself (non-standard)
B) auto-setup checks for $PGDATA/base, and manages the conf files (also
non-standard, but the non-standard part is limited to postgresql,
instead of system wide)
>> I wonder if we can assume that if you run as postgres user and connect> via local unix socket, then you have full access to database. That is,> if you su - postgres, then you can psql and pg_dumpall, (or run as> root). But if you run as normal user, lets say ncopa, and does a psql> -U postgres, then you get reject or prompted for password.>> The reasoning is that since you are running as root you do have (raw)> access to the database anyway so its no big point try protect the db> from local root user. It does make sense to protect it from other> local users and definitively from remote connections. (i though tcp> connections are off by default?)>> So technically, beeing able to do dump/restore as local root user> doesnt need be a bad idea.>> I'm not too happy about storing the password as cleartext though.>
Agreed. If pg-restore has access to /etc/conf.d/postgresql, I think we
can safely assume your pg_hba.conf must allow "trusted" authentication
over unix-sockets for the postmaster. Then pg-restore can use the
same su -l $PGUSER .... trick that the main postgresql script does.
Before going of and coding a solution, I propose:
1 Backup p*.conf files using standard Alpine Linux methods (lbu
include) This means $PGDATA/p*.conf files may exist before services are
started.
2 Keep the postgresql AUTO_SETUP code, but set AUTO_SETUP as the
default (that way, apk add postgresql && rc-service postgresql start
"just works")
3 Enhance postgresql setup to check for $PGDATA/base not $PGDATA; if
$PGDATA exists, but $PGDATA/base does not, handle any existing $PGDATA/*
files gracefully.
4 Use the pg-restore script as before; it can now depend on postgresql
even with local changes to the $PGDATA/p*.conf
5 Have pg-restore source /etc/conf.d/postgresql for the PGUSER
variable, and depend on a local unix-socket "trust" mechanism for
restoring the database.
6 use the pg-restore stop function to make a backup (if defined in
/etc/conf.d/pg-restore); start to restore a backup.
The Alternative proposal is to put the functions inside of postgresql
init script itsef. This eliminates point 5 above, but does not really
simplify anything else.
Comments?
---
Unsubscribe: alpine-devel+unsubscribe@lists.alpinelinux.org
Help: alpine-devel+help@lists.alpinelinux.org
---
On Fri, May 14, 2010 at 3:32 AM, Nathan Angelacos <nangel@nothome.org> wrote:
<snip>
>> I'm not too happy about storing the password as cleartext though.>>>> Agreed. If pg-restore has access to /etc/conf.d/postgresql,
I see no problem with sourcing /etc/conf.d/postgresql manually from
pg-restore sice they are both provided by same package.
> I think we can> safely assume your pg_hba.conf must allow "trusted" authentication over> unix-sockets for the postmaster. Then pg-restore can use the same su -l> $PGUSER .... trick that the main postgresql script does.
yes. I think we want use the su -l $PGUSER trick.
>> Before going of and coding a solution, I propose:>> 1 Backup p*.conf files using standard Alpine Linux methods (lbu include)> This means $PGDATA/p*.conf files may exist before services are started.> 2 Keep the postgresql AUTO_SETUP code, but set AUTO_SETUP as the default> (that way, apk add postgresql && rc-service postgresql start "just works")> 3 Enhance postgresql setup to check for $PGDATA/base not $PGDATA; if> $PGDATA exists, but $PGDATA/base does not, handle any existing $PGDATA/*> files gracefully.> 4 Use the pg-restore script as before; it can now depend on postgresql even> with local changes to the $PGDATA/p*.conf> 5 Have pg-restore source /etc/conf.d/postgresql for the PGUSER variable,> and depend on a local unix-socket "trust" mechanism for restoring the> database.> 6 use the pg-restore stop function to make a backup (if defined in> /etc/conf.d/pg-restore); start to restore a backup.
I think this sounds good.
> The Alternative proposal is to put the functions inside of postgresql init> script itsef. This eliminates point 5 above, but does not really simplify> anything else.>> Comments?
I think its nice to have the scripts separated, but no strog feelings
there really. Do what you think is best.
Thanks alot for following this up.
--
Natanael Copa
---
Unsubscribe: alpine-devel+unsubscribe@lists.alpinelinux.org
Help: alpine-devel+help@lists.alpinelinux.org
---
On 05/13/10 23:40, Natanael Copa wrote:
> On Fri, May 14, 2010 at 3:32 AM, Nathan Angelacos<nangel@nothome.org> wrote:> <snip>>> Comments?>> I think its nice to have the scripts separated, but no strog feelings> there really. Do what you think is best.>> Thanks alot for following this up.>
attached.