Forum OpenACS Q&A: Response to Postgres troubles
So do Debian-types, Don! It's NOT a PG specific thing. It's a Debian thing.
Part of the HUGE bonus of a Debian system is the excellent integration of packages into an entire distribution. Debian has a set of standards for how things work, how daemons are started, checks for dependencies on other packages, daemon running privs, file locations, menuing system for passing parameters to the app setup, etc. Part of the responsibilities of a Debian package maintainer making sure that the app follows "policy" on a Debian system. Nobody's perfect, so ALL packages go through the same process; they're uploaded to unstable, and banged on by all Debian users willing to do so. Bugs are reported...they may be actual application bugs, or they may be Debian package bugs. Once a package has been in testing for a certain amount of time and passes certain criteria, it automatically get moved into the testing distribution. Testing (presently known as "woody"; all releases have Toy Story-related names) is banged on HARD...and eventually becomes the next stable release.
Sorry for the long, off-topic ramble...but the Debian policy and dependency system kicks SERIOUS butt. The vast majority of the time, installing something like postresql on your system, starting from zero, goes something like:
abelard:~# apt-get install postgresql Reading Package Lists... Done Building Dependency Tree... Done The following extra packages will be installed: postgresql-client The following NEW packages will be installed: postgresql postgresql-client 0 packages upgraded, 2 newly installed, 0 to remove and 1 not upgraded. Need to get 1721kB of archives. After unpacking 4276kB will be used. Do you want to continue? [Y/n] Get:1 http://non-us.debian.org testing/non-US/main postgresql-client 7.1.3-7 [268kB] Get:2 http://non-us.debian.org testing/non-US/main postgresql 7.1.3-7 [1452kB] Fetched 1721kB in 18s (94.0kB/s) Selecting previously deselected package postgresql-client. (Reading database ... 39890 files and directories currently installed.) Unpacking postgresql-client (from .../postgresql-client_7.1.3-7_i386.deb) ... Old binaries should already have been saved. Selecting previously deselected package postgresql. Unpacking postgresql (from .../postgresql_7.1.3-7_i386.deb) ... Found an existing database directory at /var/lib/postgres/data Setting up postgresql-client (7.1.3-7) ... The file /etc/postgresql/postgresql.env provides the normal set-up for an ordinary user running PostgreSQL. It is automatically read by the wrapper script for PostgreSQL user commands in postgresql-client. Setting up postgresql (7.1.3-7) ... Installing new version of config file /etc/init.d/postgresql ... Installing new version of config file /etc/postgresql/pg_hba.conf ... Installing new version of config file /etc/postgresql/pg_ident.conf ... Installing new version of config file /etc/cron.d/postgresql ... The format of PostgreSQL's configuration files has changed radically. /etc/postgresql/postmaster.init and /etc/postgresql/pg_options are no longer used. * I had an old PG install...* Do you want me to move your old configuration to the new files? [Y/n]Y Restarting PostgreSQL database: postmaster No /usr/lib/postgresql/bin/postmaster found running; none killed. The database is in an older format that cannot be read by version 7.1 of PostgreSQL. The postinstallation script should attempt to upgrade the database automatically. If it fails, it must be done by hand. *** READ /usr/share/doc/postgresql/README.Debian.migration.gz FIRST! *** The version 7.1 postmaster cannot be started until this is done. . The postmaster has not restarted. postgresql has found a pre-existing database, and believes that it will be able to update it automatically to the new version. There is some risk to your data in this. If you have not got an up-to-date backup of /var/lib/postgres/data, you should say no at this point. In the course of the update, your existing /var/lib/postgres/data will be moved to a parallel directory and a new directory created in its place. The saved dump of your existing database will then be loaded into the new database. If you have a lot of data, this may take a considerable time; if your database is sufficiently large, it may fail for lack of disk space. The backup of /var/lib/postgres/data will be on the same filesystem; the dump file will be in /usr/lib/postgresql/dumpall/. Do you want me to continue with the automatic update? [y/N] y *dump...blah...* This is the ASCII output of the dump for you to check: -- postgresql-dump on Wed Mar 6 10:37:50 EST 2002 from version 6.5 connect template1 select datdba into table tmp_pg_shadow from pg_database where datname = 'template1'; delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; drop table tmp_pg_shadow; copy pg_shadow from stdin; kkennedy 1000 t f t t N N . connect template1 kkennedy create database mydb; connect mydb kkennedy connect - postgres CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; -- postgresql-dump completed on Wed Mar 6 10:37:55 EST 2002 On the basis of this dump, is it OK to delete the old database? [y/n] y * I don't care...just an example...* Destroying old database... Copying /var/lib/postgres/data to /var/lib/postgres/data.6.5.3-26.save Deleting /var/lib/postgres/data Creating the new database structure This database system will be initialized with username "postgres". This user will own all the data files and must also own the server process. Fixing permissions on existing directory /var/lib/postgres/data Creating directory /var/lib/postgres/data/base Creating directory /var/lib/postgres/data/global Creating directory /var/lib/postgres/data/pg_xlog Creating template1 database in /var/lib/postgres/data/base/1 DEBUG: database system was shut down at 2002-03-06 10:38:59 EST DEBUG: CheckPoint record at (0, 8) DEBUG: Redo record at (0, 8); Undo record at (0, 8); Shutdown TRUE DEBUG: NextTransactionId: 514; NextOid: 16384 DEBUG: database system is in production state Creating global relations in /var/lib/postgres/data/global DEBUG: database system was shut down at 2002-03-06 10:39:12 EST DEBUG: CheckPoint record at (0, 108) DEBUG: Redo record at (0, 108); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 514; NextOid: 17199 DEBUG: database system is in production state Initializing pg_shadow. Enabling unlimited row width for system tables. Creating system views. Loading pg_description. Setting lastsysoid. Vacuuming database. Copying template1 to template0. Success. You can now start the database server using: /usr/lib/postgresql/bin/postmaster -D /var/lib/postgres/data or /usr/lib/postgresql/bin/pg_ctl -D /var/lib/postgres/data -l logfile start Checking that the postmaster is running Starting the postmaster Starting PostgreSQL postmaster /usr/lib/postgresql/bin/postmaster -i -b /usr/lib/postgresql/bin/postgres -D /var/lib/postgres/data >/var/log/postgres.log 2>&1 & Loading the database from /usr/lib/postgresql/dumpall/db.out Reloading databases... You are now connected to database template1. select datdba into table tmp_pg_shadow from pg_database where datname = 'template1'; SELECT delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba; DELETE 0 drop table tmp_pg_shadow; DROP copy pg_shadow from stdin; You are now connected to database template1 as user kkennedy. create database mydb; CREATE DATABASE You are now connected to database mydb as user kkennedy. You are now connected as new user postgres. CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C'; CREATE CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; CREATE Reload succeeded Restarting PostgreSQL database: postmaster Stopped /usr/lib/postgresql/bin/postmaster (pid 10202). Starting PostgreSQL postmaster. postmaster successfully started Enabling the PL procedural language in all PostgreSQL databases... peer validation request: pg user=postgres, peer user = postgres, auth_arg = sameuser peer validation request: pg user=postgres, peer user = postgres, auth_arg = sameuser plpgsql is already enabled in mydb peer validation request: pg user=postgres, peer user = postgres, auth_arg = sameuser peer validation request: pg user=postgres, peer user = postgres, auth_arg = sameuser peer validation request: pg user=postgres, peer user = postgres, auth_arg = sameuser peer validation request: pg user=postgres, peer user = postgres, auth_arg = sameuser peer validation request: pg user=postgres, peer user = postgres, auth_arg = sameuser plpgsql added to template1Done. And sorry for the long post...I forgot I had the old v.6 db there from when I ran PG on this machine. I thought it'd be interesting to see how the automated dump and replace went.