X-Original-To: alpine-devel@lists.alpinelinux.org Delivered-To: alpine-devel@lists.alpinelinux.org Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.152]) by lists.alpinelinux.org (Postfix) with ESMTP id C8EF91EB598 for ; Thu, 13 May 2010 07:10:17 +0000 (UTC) Received: by fg-out-1718.google.com with SMTP id l26so2236385fgb.7 for ; Thu, 13 May 2010 00:10:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=/say6dzU4tW8q5gn+4ZJLXacGkUZwA5huxFdHSFLXMA=; b=OasFe0ApyVjF27BrmaE+hsxIVL18H3AYVpf6TGJjQIwQdSmgRlHD5E7xW0n4TVPhOC Dr2QyF+kJMU004anbodAGRWuO4FkNo/UnOV7e0JlOJAGE/WFc8zQ8Pd8TgrlorUswzth In+GuONwV8vwqLOZyM3XDlhFcWiRU2OFefBc4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=di7qblIX/KLUmGttzV91hJOoM2+nB/Ujt6bTk5K/DncJeWe+++ZfzR1uFOEgB8vC1Q QPjXV6+h8i1JNb8zKMjwGHSapxpgc8sbGoKcZM0GLbnaZDuzZv6FEtoZzFHR85M5pItj SQACbc672xgRtlvEGvYVS8LeVcv7QfoY6cjVo= X-Mailinglist: alpine-devel Precedence: list List-Id: Alpine Development List-Unsubscribe: List-Post: List-Help: List-Subscribe: MIME-Version: 1.0 Received: by 10.204.84.142 with SMTP id j14mr91589bkl.124.1273734616188; Thu, 13 May 2010 00:10:16 -0700 (PDT) Received: by 10.204.55.4 with HTTP; Thu, 13 May 2010 00:10:16 -0700 (PDT) In-Reply-To: <4BEB97F0.4060304@nothome.org> References: <4BEB97F0.4060304@nothome.org> Date: Thu, 13 May 2010 09:10:16 +0200 Message-ID: Subject: Re: [alpine-devel] Patch to postgresql init script From: Natanael Copa To: Nathan Angelacos Cc: Alpine-devel Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable On Thu, May 13, 2010 at 8:10 AM, Nathan Angelacos wrot= e: > > Attached is a patch for /etc/init.d/postgresql =A0and /etc/conf.d/postgre= sql Thanks! > A lbu commit of the /var/lib/postgresql/x.x/data directory takes a very l= ong > 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. =A0This 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. > =A0This patch attempts to address that issue > > Limitations: =A0The 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=3D/some/path/he= re.gz I'd prefer using uncompressed dump since lbu will compress it again. > postgresql stores the pg_hba.conf (access controls) and postgresql.conf i= n > $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.con= f > =A0and 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. --=20 Natanael Copa --- Unsubscribe: alpine-devel+unsubscribe@lists.alpinelinux.org Help: alpine-devel+help@lists.alpinelinux.org ---