Forum OpenACS Q&A: Response to Postgres troubles

Collapse
16: Response to Postgres troubles (response to 1)
Posted by Ken Kennedy on 03/06/02 04:35 PM
I know that Debian has their own independent rating system, if you will. It's just that I trust the PG group inherently.

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