Arch Linux

soloport

Member

Registered: 2005-03-01

Posts: 442

Postgres 7.4 to 8.0 Migration HOWTO

Here's how I migrated from Postgres 7.4 to 8.0 -- in a nutshell:

1) Upgrade the software: pacman -Sy postgres
2) Manually create a new tree:
  a) mkdir /var/lib/postgres/data.new
  b) chown postgres.postgres /var/lib/postgres/data.new
  c) su postgres -c "initdb -D /var/lib/postgres/data.new"
3) Shut down connections and dump latest data:
  a) /etc/rc.d/[httpd|other] stop
  b) pg_dumpall -U postgres > pg_dumpall.sql
4) Stop and manually restart Postgres:
  a) /etc/rc.d/postgresql stop
  b) su postgres -c "pg_ctl -D /var/lib/postgres/data.new -W start"
5) Restore data and services:
  a) psql -U postgres template1 < pg_dumpall.sql
  b) /etc/rc.d/[httpd|other] start
6) Restore normal behavior:
  a) mv /var/lib/postgres/data /var/lib/postgres/data.yymmdd
  b) Repeat step 2-5, replacing "data.new" with "data"

Hopefully steps 4 and 5 are done in mere minutes.  If your migration window is even more relaxed than that, you  might want to move step 1 down to between 4 and 5 (first time through).

Not: This worked for a standalone server.  For a load-balanced suite of servers, the job is, of course, much less painful ;-)

While we're on the subject, here's a Postgres backup script:

#!/bin/bash
# keep last two weeks of backups -- TODO: incrementals???
DAYS=14
PGSQL_BACKUPS=/home/httpd/postgresql

# crytical if in /etc/cron.hourly/ but still good if in /etc/cron.daily/
LOCK=/var/lock/backups.cron
if [ -f $LOCK ]; then
    echo "Help! Daily backups may be stuck." | /usr/bin/mail -s "Daily Backups" root
    echo "Help! Daily backups may be stuck." | /usr/bin/mail -s "Daily Backups" 
80012312345@mobile.att.net
    exit
fi
/bin/touch $LOCK

# vacuum Postgres -- cleanliness is next to...?
/usr/bin/vacuumdb -afq

# dump Postgres
date=`date +"%y%m%d"`
/usr/bin/pg_dumpall -U postgres > $PGSQL_BACKUPS/pg_dumpall_$date.sql
/bin/chmod 600 $PGSQL_BACKUPS/pg_dumpall_$date.sql

i=0
declare -a files
files=`ls -1r $PGSQL_BACKUPS/pg_dumpall_*.sql 2> /dev/null`
for file in $files; do
  if [[ $i -lt $DAYS ]]; then
      let i=$i+1
      continue
  fi
  /bin/rm -f $file
done

/bin/rm -f $LOCK

Not pretty, but works for me  8)

[edit] Silly me.  Initially was going to ask if anyone wanted a contribution to the Wiki on this subject.  Then I went off into the weeds and forgot to post on a more appropriate thread.  Sorry, moderators...

android

Member

From: San Diego

Registered: 2003-04-18

Posts: 160

Re: Postgres 7.4 to 8.0 Migration HOWTO

Hi Soloport,

I'm in the process of starting postgresql on arch for the first time.

That wiki contribution: "PostgreSQL on Arch HOWTO" would sure come in handy right now.

There seem to be quite a few little manual steps needed to bring up the server for the first time (user passwd, conf file, etc).

Of course, once it's up, some guidance on administering postgres would be really helpful too 8-)

I've got a little experience with MySQL but I'm a total newbie to PostgreSQL.

So Thanks in Advance for any guidance you could offer on the wiki.

Ciao...

Android

soloport

Member

Registered: 2005-03-01

Posts: 442

Re: Postgres 7.4 to 8.0 Migration HOWTO

Many years ago, I migrated from MySQL to Postgres.  I still remember some gotchas.  Here's what I recall:
* In MySQL everything seems to revolve around yet-another-database -- a lot like the flat file DB way
* You'll want to get used to ONE database in Postgres -- it's just a mater of naming your tables with prefixes, like pre_table, pre_table_view, etc. -- this is the "normal" RDBMS way, as I've learned
* You are going to learn all the good DBA habbits by using Postgres -- and not have to unlearn bad habbits, later on, when you are asked to migrate to another RDBMS
* Every transaction (e.g. INSERT, UPDATE) starts with an implied BEGIN and COMMIT
* If you need to issue a whole string of transactions, start with a BEGIN and end with a COMMIT, regardless of whether you care about rollbacks
* If you just issue a series of transactions (without an explicit BEGIN & COMMIT) each and every transaction will be accompanied by its own BEGIN ... COMMIT (very slow)
* User management is much more meaningful in Postgres
* Administration of Postgres is much easier
* Postgres is scalable where it really counts
* With MySQL developers don't know they're working three times as hard as they need to -- because they have to put so much logic in the application (e.g. C/C++ or PHP)
* You'll go nuts when you discover how much of your application logic can be relegated to your RDBMS
* You'll be ecstatic when you discove the SPEED with which your application runs when your RDBMS handles the complex logic that you were so used to puting in the application (slow)

[edit] Ok, have re-read your post.  What you want is a Wiki HOWTO.  I'll see if I can write one, soon.

android

Member

From: San Diego

Registered: 2003-04-18

Posts: 160

Re: Postgres 7.4 to 8.0 Migration HOWTO

I'd say that's a really good start! (to the Wiki HOWTO).

Thanks for your insight.

I'm bringing up the quasar business accounting system, which is based on postgresql. I'm still learning the ropes as far as admin tools etc.

Thanks again for the info! I'm looking forward to the Wiki entry 8-)

Android

heaven

Member

Registered: 2009-11-24

Posts: 32

Re: Postgres 7.4 to 8.0 Migration HOWTO

Here's how I migrated from Postgres 7.4 to 8.0 -- in a nutshell:

1) Upgrade the software: pacman -Sy postgres
2) Manually create a new tree:
  a) mkdir /var/lib/postgres/data.new
  b) chown postgres.postgres /var/lib/postgres/data.new
  c) su postgres -c "initdb -D /var/lib/postgres/data.new"
3) Shut down connections and dump latest data:
  a) /etc/rc.d/[httpd|other] stop
  b) pg_dumpall -U postgres > pg_dumpall.sql
4) Stop and manually restart Postgres:
  a) /etc/rc.d/postgresql stop
  b) su postgres -c "pg_ctl -D /var/lib/postgres/data.new -W start"
5) Restore data and services:
  a) psql -U postgres template1 < pg_dumpall.sql
  b) /etc/rc.d/[httpd|other] start
6) Restore normal behavior:
  a) mv /var/lib/postgres/data /var/lib/postgres/data.yymmdd
  b) Repeat step 2-5, replacing "data.new" with "data"

Thank you, it helped!

bernarcher

Forum Fellow

From: Germany

Registered: 2009-02-17

Posts: 2,281

Re: Postgres 7.4 to 8.0 Migration HOWTO

XXL Necro!
Closing.

---

To know or not to know ...
... the questions remain forever.