Subject: [alpine-devel] Patch to postgresql init script

From: Nathan Angelacos <[email-masked]>
Date: Wed, 12 May 2010 23:10:56 -0700

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.

>From d4f7f491ff2fe5628fad55e5777129e5b5f2a636 Mon Sep 17 00:00:00 2001
From: Nathan Angelacos <[email-masked]>
Date: Wed, 12 May 2010 22:36:57 -0700
Subject: [PATCH] Patches to allow postgresql to restore a pg_dump made by an lbu commit

These patches cause postgresql to load a gzip archive of a pg_dump file
defined in /etc/conf.d/postgresql on initial load.

This allows postgresql to "restore its state" on reboot
---
 main/postgresql/postgresql.confd |    6 +++++
 main/postgresql/postgresql.initd |   43 ++++++++++++++++++++++++++++++++++---
 2 files changed, 45 insertions(+), 4 deletions(-)

diff --git a/main/postgresql/postgresql.confd b/main/postgresql/postgresql.confd
index 4fe28a4..5361e1f 100644
--- a/main/postgresql/postgresql.confd
+++ b/main/postgresql/postgresql.confd
@@ -7,6 +7,12 @@ PGUSER="postgres"
 # PostgreSQL Group
 PGGROUP="postgres"
 
+# Alpine Linux LBU can create a database backup with /etc/lbu/pre-package.d/
+# If you create a dump with pg_dumpall -U postgres | gzip -c >/root/pgdatabases.gz
+# uncommenting the next line will restore the database on reboot
+# For more information see the lbu docs on www.alpinelinux.org
+# LBU_BACKUP="/root/pgdatabases.gz"
+
 # Extra options to run postmaster with, e.g.:
 # -N is the maximal number of client connections
 # -B is the number of shared buffers and has to be at least 2x the value for -N
diff --git a/main/postgresql/postgresql.initd b/main/postgresql/postgresql.initd
index 23b0acf..b4fa762 100644
--- a/main/postgresql/postgresql.initd
+++ b/main/postgresql/postgresql.initd
@@ -2,6 +2,7 @@
 # Copyright 1999-2008 Gentoo Foundation
 # Distributed under the terms of the GNU General Public License v2
 # $Header: /var/cvsroot/gentoo-x86/dev-db/postgresql-server/files/postgresql.init-8.3,v 1.4 2008/09/28 22:53:02 caleb Exp $
+# Modifications for database Reload on Alpine Linux - 2010
 
 opts="${opts} reload setup"
 
@@ -11,18 +12,52 @@ depend() {
 }
 
 checkconfig() {
-	[ -d "$PGDATA" ] && return 0
-
-	if [ -z "$AUTO_SETUP" ] || [ "$AUTO_SETUP" = "no" ]; then
+	if [ ! -d "$PGDATA" ] ; then
 		eerror "Directory not found: $PGDATA"
 		eerror "Please make sure that PGDATA points to the right path."
 		eerror "You can run '/etc/init.d/postgresql setup' to setup a new database cluster."
 		return 1
 	fi
-	setup
+}
+
+restore_lbu_dump() {
+	# this function calls start, which calls this function.  Exit early if we called start
+	[ "$LBU_RESTORE_ACTIVE" ] && return
+	LBU_RESTORE_ACTIVE=1
+
+	if [ -e "$LBU_BACKUP" ] ; then
+		ewarn "Found an lbu backup file - restoring it."
+		if [ -e "$PGDATA"/base ] ; then
+			eerror "The database directories are already created - not restoring the backup"
+			return 1
+		fi
+
+		# If the pg_hba.conf and friends exist, move them
+		local tmpdir=$( dirname "$PGDATA" )/tmp
+		mkdir -p "$tmpdir"
+		mv "$PGDATA"/* "$tmpdir" 2>/dev/null
+		
+		rm -rf "$PGDATA"
+		einfo "Initializing a new database directory"
+		setup
+		start
+
+		einfo "Extracting the lbu backup file"
+		# assume it is gzip
+		gunzip -c "$LBU_BACKUP" | psql -U postgres >/dev/null 2>/dev/null
+
+		# move the pg_hba.conf and friends
+		mv $tmpdir/* "$PGDATA" 2>/dev/null 
+		rm -rf $tmpdir 2>/dev/null
+		
+		rm -f "$LBU_BACKUP"
+		stop
+		return $?
+	fi
 }
 
 start() {
+	restore_lbu_dump 
 	checkconfig || return 1
 
 	ebegin "Starting PostgreSQL"
-- 
1.7.1

---

From: Natanael Copa <[email-masked]>
Date: Thu, 13 May 2010 09:10:16 +0200

> 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.

> pg_dumpall of the databases, and then commit the archive.  This is much

(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.

> 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:  [email-masked]
Help:         [email-masked]
---

---

From: Nathan Angelacos <[email-masked]>
Date: Thu, 13 May 2010 18:32:22 -0700

On 05/13/10 00:10, Natanael Copa wrote:

<snip>

> (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>

> 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.

> 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'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?

---

---

From: Natanael Copa <[email-masked]>
Date: Fri, 14 May 2010 08:40:20 +0200

On Fri, May 14, 2010 at 3:32 AM, Nathan Angelacos <[email-masked]> wrote:
<snip>

> 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.

>  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")
>  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.

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

---

---

From: Nathan Angelacos <[email-masked]>
Date: Mon, 17 May 2010 00:23:39 -0700

On 05/13/10 23:40, Natanael Copa wrote:
> On Fri, May 14, 2010 at 3:32 AM, Nathan Angelacos<[email-masked]>  wrote:
> <snip>

> Thanks alot for following this up.

attached.
diff --git a/main/postgresql/pg-restore.confd b/main/postgresql/pg-restore.confd
index 18bff4c..84a179f 100644
--- a/main/postgresql/pg-restore.confd
+++ b/main/postgresql/pg-restore.confd
@@ -10,7 +10,7 @@
 #
 #KEEP_DUMP=yes
 
-# If you enable the KEEP_DUMP bove you probably also want the --clean option
+# If you enable KEEP_DUMP above you probably also want the --clean option
 # the the pg_dumpall command.
 #PG_DUMPALL_OPTS="--clean"
 
diff --git a/main/postgresql/pg-restore.initd b/main/postgresql/pg-restore.initd
index aaf07cb..91c69d1 100644
--- a/main/postgresql/pg-restore.initd
+++ b/main/postgresql/pg-restore.initd
@@ -1,5 +1,7 @@
 #!/sbin/runscript
 
+. /etc/conf.d/postgresql
+
 opts="${opts} dump restore"
 
 depend() {
@@ -7,18 +9,22 @@ depend() {
 }
 
 restore() {
-	[ -z "$PGDUMP" ] && return 0
+	yesno "$PGDUMP" && return 0
 	ebegin "Restoring PostgreSQL $PGDUMP"
-	psql -U ${PG_USER:-postgres} ${PSQL_OPTS} -f "$PGDUMP"
-	eend $?
-	if [ -z "$KEEP_DUMP" ]; then
-		rm -f "$PGDUMP"
-	fi
+	psql -U ${PG_USER:-postgres} ${PSQL_OPTS} -f "$PGDUMP" >/dev/null 2>/dev/null
+	yesno "$KEEP_DUMP" || rm -f "$PGDUMP"
+
+	su -l ${PGUSER} \
+                -c "env PGDATA=\"${PGDATA}\" /usr/bin/pg_ctl reload " >/dev/null
+	eend $res
 }
 
 dump() {
-	[ -z "$PGDUMP" ] && return 0
+	yesno "$PGDUMP" && return 0
+	mkdir -p "$( dirname "$PGDUMP" )"
+	ebegin "Saving PostgreSQL databases to $PGDUMP"
 	pg_dumpall -U ${PG_USER:-postgres} ${PG_DUMPALL_OPTS} -f "$PGDUMP"
+	eend $?
 }
 
 start() {
diff --git a/main/postgresql/postgresql.confd b/main/postgresql/postgresql.confd
index 4fe28a4..29ef20d 100644
--- a/main/postgresql/postgresql.confd
+++ b/main/postgresql/postgresql.confd
@@ -52,5 +52,5 @@ WAIT_FOR_START="-w"
 #   export R_HOME="/usr/lib/R"
 
 # Automatically set up a new database if missing on startup
-#AUTO_SETUP=yes
+AUTO_SETUP=yes
 
diff --git a/main/postgresql/postgresql.initd b/main/postgresql/postgresql.initd
index 23b0acf..2de91d0 100644
--- a/main/postgresql/postgresql.initd
+++ b/main/postgresql/postgresql.initd
@@ -2,6 +2,7 @@
 # Copyright 1999-2008 Gentoo Foundation
 # Distributed under the terms of the GNU General Public License v2
 # $Header: /var/cvsroot/gentoo-x86/dev-db/postgresql-server/files/postgresql.init-8.3,v 1.4 2008/09/28 22:53:02 caleb Exp $
+# Modifications to support Alpine Linux pg-restore

@@ -11,10 +12,11 @@ depend() {
 }
 
 checkconfig() {
-	[ -d "$PGDATA" ] && return 0
+	[ -d "$PGDATA/base" ] && return 0
+	echo "checking config"
 
-	if [ -z "$AUTO_SETUP" ] || [ "$AUTO_SETUP" = "no" ]; then
-		eerror "Directory not found: $PGDATA"
+	if [ -z "$AUTO_SETUP" ] ; then
+		eerror "Database not found at: $PGDATA"
 		eerror "Please make sure that PGDATA points to the right path."
 		eerror "You can run '/etc/init.d/postgresql setup' to setup a new database cluster."
 		return 1
@@ -101,18 +103,36 @@ reload() {
 
 setup() { 
 	ebegin "Creating a new PostgreSQL database cluster" 
-	rmdir "${PGDATA}" 2>/dev/null
-	if [ -d "${PGDATA}" ] ; then 
-		eend 1 "${PGDATA} already exist" 
+	
+	if [ -d "${PGDATA}/base" ] ; then 
+		eend 1 "${PGDATA}/base already exists" 
 		return 
 	fi 
-	mkdir -p "${PGDATA}" 
+
+	mkdir -p "${PGDATA}"  2>/dev/null
+
+	# If the pg_hba.conf and friends exist, move them
+	local tmpdir="$( dirname "$PGDATA" )/tmp"
+	mkdir -p "${tmpdir}" >/dev/null
+	echo mv "${PGDATA}"/* "${tmpdir}" 
+	mv "${PGDATA}"/* "${tmpdir}"  2>/dev/null
+
+	rm -rf "${PGDATA}"/* 2>/dev/null
 	chown -Rf postgres:postgres "${PGDATA}" 
 	chmod 0700 "${PGDATA}" 
 	cd "${PGDATA}" # to avoid the: could not change directory to "/root" 
 	su -c "/usr/bin/initdb --pgdata ${PGDATA}" postgres
 	einfo "You can use the '/etc/init.d/postgresql' script to run PostgreSQL instead"
 	einfo "of 'pg_ctl'."
-	eend $? 
+	local res=$?
+
+	# move the pg_hba.conf and friends
+	mv $tmpdir/* "$PGDATA" 2>/dev/null 
+	rm -rf $tmpdir 2>/dev/null
+
+	# Do not send a SIGHUP to postmaster;  its not necessary for a new database
+	# and allows pg-restore to do a blind restore of an old database
+
+	eend $res
 }

---

From: Natanael Copa <[email-masked]>
Date: Mon, 17 May 2010 19:08:07 +0000

> attached.

commited with nangel as author.

thanks!

-- 
Natanael Copa

---