Beta

Slashdot: News for Nerds

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

quick and dirty postgresql update- tutorial

marcello_dl (667940) writes | more than 6 years ago

Debian

Note, this procedure involves some downtime for the server.
First dump existing databases, e.g.

# su - postgres -c 'pg_dump -C my_database > /tmp/my_database.sql'

If you have user putting content into database this step could be done later, just before importing the sql in the new cluster. Remember to take down db clients if you care for db consistency.

Then install new version, e.g.

# aptitude install postgresql-8.3

Note, this procedure involves some downtime for the server.
First dump existing databases, e.g.

# su - postgres -c 'pg_dump -C my_database > /tmp/my_database.sql'

If you have user putting content into database this step could be done later, just before importing the sql in the new cluster. Remember to take down db clients if you care for db consistency.

Then install new version, e.g.

# aptitude install postgresql-8.3

when one installs a new version of postgres under debian, the old version stays installed and a new cluster is automatically created. The new version server is configured for an alternative port (5433). Watch for the messages/logs from the package manager.

Then import old users into the new cluster, take into account the listening port run the updated client as the bash command "psql" defaults to the old version.

# su - postgres
# createuser a_user --port 5433

Then copy or update the pg_hba.conf settings to the one in the new cluster in /etc/postgresql/8.3/main/pg_hba.conf

Do not forget to reload the settings.
# /etc/init.d/postgresql-8.3 reload

Then import the databases
# su - postgres
$ /usr/lib/postgresql/8.3/bin/psql --port 5433

Then configure the new server for the existing server port, e.g.
# /etc/init.d/postgresql-8.3 stop
edit /etc/postgresql/8.3/main/postgresql.conf:
    port=5432

Then stop the db clients (eg. it's downtime for my dynamic sites hosted with lighttpd), take down old db, take up new one, restart db clients /etc/init.d/lighttpd stop /etc/init.d/postgresql-8.2 stop /etc/init.d/postgresql-8.3 start
    netstat -nat /etc/init.d/lighttpd start

test the db clients (restart your browser for webapps to avoid using stale session data), if all is well we must remove old postgresql else at reboot it is restarted, conflicting with the newest server version.

Since the debian remove scripts assume the server is up we must take the old server back up- we need to configure the old server for an alternative port so both versions can be up at the same time, e.g.

edit /etc/postgresql/8.3/main/postgresql.conf:
    port=5434

# /etc/init.d/postgresql-8.2 start
# aptitude purge postgresql-8.2

This is all.
Note. It is better to upgrade incrementally than doing it say, from 8.1 to 8.3
There might be ways to reduce downtime, with WAL for example.

cancel ×

comment

Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...