Bug 272969 – Update for PostgreSQL Guide with Section Upgrading PostgreSQL

• Product: [OLD] Docs on www.gentoo.org
• Status: RESOLVED FIXED

[Description Martin Scholz 2009-06-06 19:14:02 UTC]

It would be helpful for every newbie to gentoo to have a section in the PostgreSQL Guide on how to perform an upgrade from one version to another.
I did the whole stuff after an hour of searching with the holy google oracle and found an solution on http://en.gentoo-wiki.zugaina.org/index.php/PostgreSQL#Upgrading_Postgresql


Reproducible: Always

Steps to Reproduce:
Here is a solution for the Manual

Actual Results:  
Missing part

Expected Results:  
in english, sorry for the semi-xml-style... :

<chapter>
<title> Upgrading Postgresql</title>
<section>
<title>Database backup</title>
When upgrading postgreSQL emerge tells
<pre caption="emerge tells">
 * Postgres n.n.n cannot upgrade your existing databases, you must
 * use pg_dump to export your existing databases to a file, and then
 * pg_restore to import them when you have upgraded completely.
</pre>
The problem here for a newbie is that emerge speaks with forked tongue, it's not pg_restore one need, but more on this below.

As mentioned, you first need to backup your databases. There are two ways: 
1. If you have multiple databases, it is easier to use the pg_dumpall program. This causes all data in the database to be dumped to the a single file. The syntax is:
<pre>
 pg_dumpall -U sqluser > /backupplace/backup.sql 
</pre>
Because pg_dumpall needs to connect to each database separately, it will ask you for the user password each time it tries to connect to a new database. So configure an account to use trust or ident authentication in pg_hba.conf before running pg_dumpall if you want to avoid this. Or, use the "postgres" user. Note. If you have chanced pg_hba.conf then you need to back that up. 
2. If you have only one database, it is also possible to backup only this one (but you will loose your self created internal postgresql users ... if you doesn't back them up). You need to perform on the terminal something like this:
<pre>
 pg_dump -C -U sqlusr database1 > /backupplace/backup1.sql 
</pre>
You'll need to repeat the above for each database you've got using different output filenames each time.
To backup the self created internal postgresql users you need to run the following:
<pre>
pg_dump -U sqlusr -s database1 > /backupplace/users.sql
</pre>
You can also do
<pre>
pg_dumpall --globals-only > globals.pgsql
</pre>
when dumping individual database with pg_dump. That way, you'll have the separate databases, and the globals (like users). 
</section>

<section>
<title>Backup of database-instance and Upgrade to the new version</title>
Having backed up your database(s) to files, stop postgresql:
<pre>
/etc/init.d/postgresql stop
</pre>
You then need to "remove" the database directory. You'll remember that emerge says:
<pre>
 * You must remove your entire database directory to continue.
 * (database directory = /var/lib/postgresql).
</pre>
If you're paranoid like me then rather than remove it what you'll do is move it to one side thus:
<pre>
cd /var/lib
mv postgresql postgresql.old
</pre>
Now let emerge update postgresql. Once that's done re-install it using the emerge --config command used above.
<pre>
emerge -uDN postgresql libpq
emerge --config =dev-db/postgresql-n.n.n
</pre>
After that, you should set up your config files by copying your pg_hba.conf and/or (portions of your) postgresql.conf file from your backup to the new database location (remeber both files are still backuped under /var/lib/postgresql.old). Then fire postgresql up so you can start populating the database with your data again.
<pre>
/etc/init.d/postgresql start
</pre>
</section>

<section>
<title>Restoring your Databases to the new postgreSQL-instance</title>
Here are two possible ways depending on the way you backuped your databases above.
1. If you used the pg_dumpall method, we can simply use the following command to restore your entire database (including self-created users):
<pre>
 psql template1 < /backupplace/backup.sql 
</pre>
We do this from the command line as user postgres, so make sure that backup.sql is readable by this user. 
2. Remember to create the user(s) (in my case sql-ledger) and each database (in my case the-hug). Then use psql (yup, not pg_restore) to restore the database(s) thus:
<pre>
 psql -U sqluser template1 < /backupplace/database1.sql 
</pre>
If you have large databases, it is possible to use some command-line options of <uri link="http://www.postgresql.org/docs/8.0/static/app-pg-dumpall.html">pg_dumpall</uri> to output the sql code to an archive. You will need to use <uri link="http://www.postgresql.org/docs/8.0/static/app-pgrestore.html">pg_restore</uri> to restore your database then though.
</section>

<section>
<title>Finishing the Upgrade</title>
The postgreSQL-Manual mentions that after an restore it is wise that you run ANALYZE on each database so the optimizer has useful statistics. You can also run 
<pre>
vacuumdb -a -z 
</pre>
to analyze all databases.
Remember that if this fails you can always use emerge to fall back to the old release and then restore your database which, thanks to your foresight, still lurks in /var/lib/postgresql.old . For more info see the <uri link="http://www.postgresql.org/docs/current/static/install-upgrading.html">postgres upgrade FAQ entry</uri>.
</section>
</chapter>

for more information see:
http://en.gentoo-wiki.zugaina.org/index.php/PostgreSQL#Upgrading_Postgresql

[Comment 1 nm (RETIRED) 2009-06-14 10:29:12 UTC]

Unfortunately, we cannot add this content to our document because of licensing issues. The wiki has had a history of license of license issues due to administrators and users blanket-relicensing all content many times, regardless of the limitations or specifications of the original license.

Also, the source page is a clone of the original wiki, and is itself two years out-of-date, so its reliability cannot be verified.

[Comment 2 Martin Scholz 2009-06-14 21:49:44 UTC]

(In reply to comment #1)
> Unfortunately, we cannot add this content to our document because of licensing
> issues. The wiki has had a history of license of license issues due to
> administrators and users blanket-relicensing all content many times, regardless
> of the limitations or specifications of the original license.
> 
> Also, the source page is a clone of the original wiki, and is itself two years
> out-of-date, so its reliability cannot be verified.
> 
Would here a totaly new writen section for the upgrade-process help? if yes let me know, because the procedure in the above section is exactly what one has to do for upgrading postgresql. If interested let me know.

[Comment 3 nm (RETIRED) 2009-06-14 22:29:04 UTC]

(In reply to comment #2)
> Would here a totaly new writen section for the upgrade-process help? if yes let
> me know, because the procedure in the above section is exactly what one has to
> do for upgrading postgresql. If interested let me know.
 
If you can do it without copying the wiki content, then yes! An upgrade section could be very useful.

I CCed some folks who know postgresql; they can vet the upgrade section for technical accuracy.

[Comment 4 nm (RETIRED) 2009-07-16 23:20:46 UTC]

Please do reopen once you've got an upgrade chapter for us to review. We appreciate your help!

[Comment 5 Marc Schiffbauer 2011-02-04 13:33:10 UTC]

I do not have a chapter, but a working way to upgrade from 8.4 to 9.0:

- Install 9.0.1 (should come with regula world update)
- Stop 8.4 server (/etc/init.d/postgresql-8.4 stop)
- Initialaze 9.0: 
  - eselect postgresql set-all 9.0
  - emerge --config =dev-db/postgresql-server-9.0.1
- Migrate data: 
  - su - postgres
  - pg_upgrade -b /usr/lib64/postgresql-8.4/bin -B /usr/lib64/postgresql-9.0/bin -d /var/lib/postgresql/8.4/data -D /var/lib/postgresql/9.0/data
  - exit
- Migrate configuration (pg_hba.conf etc ) if required
- Start new cluster: /etc/init.d/postgresql-9.0 start
- Optimize db as suggested by pg_upgrade:
  - su - postgres
  - vacuumdb --all --analyze-only
- Check that new cluster works as expected
- Delete old 8.4 cluster data: /var/lib/postgresql/delete_old_cluster.sh
- Remove old packages: emerge -C =dev-db/postgresql-base-8.4.5 =dev-db/postgresql-server-8.4.5 =virtual/postgresql-base-8.4 =virtual/postgresql-server-8.4
- Cleanup: rmdir /var/lib/postgresql/8.4