The FreeBSD Diary

The FreeBSD Diary (TM)

Providing practical examples since 1998

If you buy from Amazon USA, please support us by using this link.
[ HOME | TOPICS | INDEX | WEB RESOURCES | BOOKS | CONTRIBUTE | SEARCH | FEEDBACK | FAQ | FORUMS ]
Scripts / handy tips
 New Topic  |  Go to Top  |  Go to Topic  |  Search  |  Log In   Newer Topic  |  Older Topic 
 Upgrading PostgreSQL
Author: Dan 
Date:   19-12-07 05:53

This applies mostly to upgrading PostgreSQL when you need to do a dump and a restore. In this case, I was upgrading from 8.1 to 8.2. This approach requires downtime. I did this tonight. I am repeating the steps from memory, so I hope I didn't miss anything.

Upgrade postgresql-client first. This will give you the pg_dumpall for the target database.

pg_dump -U pgsql > db.out

Stop the server: /usr/local/etc/rc.d/postgresql stop

mv the existing data directory out of the way:
cd /usr/local/pgsql
mv data data.8.1

You will need files from there. For me, it was server.crt, server.key, pg_hba.conf, and postgresql.conf.

Upgrade the server.

Init the db: /usr/local/etc/rc.d/postgresql stop

copy over your files that you saved (see above). You may wish to copy/paste the bits you need.

Start the server: /usr/local/etc/rc.d/postgresql start

Upgrade docs + contrib if you need/want them

load the data: psql -U postgres < db.out

done.

--
Webmaster

Reply To This Message
 Forum List  |  Threaded View   Newer Topic  |  Older Topic 


 Forum List  |  Need a Login? Register Here 
 User Login
 User Name:
 Password:
 Remember my login:
   
 Forgot Your Password?
Enter your email address or user name below and a new password will be sent to the email address associated with your profile.
How to get the most out of the forum

phorum.org