Author: poeml Date: Fri Aug 14 12:35:22 2009 New Revision: 7762 URL: http://svn.mirrorbrain.org/viewvc/mirrorbrain?view=revision&revision=7762 Log: docs: more directions regarding upgrading PostgreSQL Modified: trunk/docs/upgrading.rst Modified: trunk/docs/upgrading.rst ============================================================================== --- trunk/docs/upgrading.rst Thu Aug 13 17:35:11 2009 (r7761) +++ trunk/docs/upgrading.rst Fri Aug 14 12:35:22 2009 (r7762) _at_@ -6,6 +6,9 @@ Upgrading PostgreSQL -------------------- +General notes +^^^^^^^^^^^^^ + When upgrading PostgreSQL, it is important to look at the version number difference. If the third digit changes, no special procedure is needed (except when the release notes explicitely hint about it). _at_@ -40,43 +43,57 @@ -.. - Notes for upgrade walkthrough - +Offline upgrade +^^^^^^^^^^^^^^^ + +The following console transcripts should give an idea about upgrading an +PostgreSQL installation. It was done on an openSUSE system, but a similar +procedure should work on other platforms. + +The upgrade in this example is done while the database is taken offline, i.e. +you need to plan for a downtime of the server. The cron daemon is stopped so +that there are no attempted writes to the database. :program:`pg_dumpall` is +used to save the complete database to a file:: + root_at_doozer ~ # rccron stop Shutting down CRON daemon done root_at_doozer ~ # su - postgres postgres_at_doozer:~> pg_dumpall > SAVE - postgres_at_doozer:~> - - + postgres_at_doozer:~> exit root_at_doozer ~ # rcpostgresql stop - Shutting down PostgreSQLserver stopped done - - - >>>> Run the update here <<<< + Shutting down PostgreSQL server stopped done +At this point, you would upgrade the PostgreSQL software. + +Next, the :file:`data` directory is moved away, a new one created, and the dump +loaded into it:: + root_at_doozer ~ # old /var/lib/pgsql/data moving /var/lib/pgsql/data to /var/lib/pgsql/data-20090728 root_at_doozer ~ # rcpostgresql start Initializing the PostgreSQL database at location /var/lib/pgsql/data done Starting PostgreSQL done root_at_doozer ~ # - - + + root_at_doozer ~ # su - postgres postgres_at_doozer:~> psql template1 -f SAVE [...] +Now, the authentication setup and the configuration need to be migrated from +the old install to the new one:: + postgres_at_doozer:~> cp data/pg_hba.conf data/pg_hba.conf.orig postgres_at_doozer:~> cp data/postgresql.conf data/postgresql.conf.orig postgres_at_doozer:~> vi -d data-20090728/pg_hba.conf data/pg_hba.conf postgres_at_doozer:~> vi -d data-20090728/postgresql.conf data/postgresql.conf +Finally, restart PostgreSQL, Apache and cron:: - - + root_at_doozer ~ # rcpostgresql restart + Shutting down PostgreSQL server stopped done + Starting PostgreSQL done root_at_doozer ~ # rcapache2 reload Reload httpd2 (graceful restart) done root_at_doozer ~ # rccron start _at_@ -84,41 +101,20 @@ +Online upgrade +^^^^^^^^^^^^^^ + +Using a second PostgreSQL daemon, started temporarily, an online +upgrade can be performed as follows. + +First, create space for the temporary database:: - - mirrordb: - cron stop on batavia510 - cron stop on mirrordb - repopusher stop - - - - - - - - - - - - - - - - - - - - using a temporary PostgreSQL daemon: - - .. - # mkdir /space/pgsql-tmp - # chown postgres:postgres /space/pgsql-tmp - # su - postgres - .. - postgres_at_mirrordb:~> - - + root_at_mirrordb ~ # mkdir /space/pgsql-tmp + root_at_mirrordb ~ # chown postgres:postgres /space/pgsql-tmp + +Create the new (temporary) database:: + + root_at_mirrordb ~ # su - postgres postgres_at_mirrordb:~> initdb /space/pgsql-tmp/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. _at_@ -157,3 +153,53 @@ postgres_at_mirrordb:~> + +Copy the configuration and the authentification setup to the temporary database:: + + postgres_at_mirrordb:~> cp /space/pgsql/data/postgresql.conf /space/pgsql-tmp/data/ + postgres_at_mirrordb:~> cp /space/pgsql/data/pg_hba.conf /space/pgsql-tmp/data/ + +.. note:: + The second database server will need RAM — maybe it will be necessary to + adjust the ``shared_buffers`` setting in :file:`postgresql.conf` for both + daemons, so they don't try allocate more memory than physically available. + +Next, change the port in the temporary :file:`postgresql.conf` from 5432 to +5433 and start the second PostgreSQL server:: + + postgres_at_mirrordb:~> vi /space/pgsql-tmp/data/postgresql.conf + postgres_at_mirrordb:~> postgres -D /space/pgsql-tmp/data + +.. note:: + This assumes that Apache is configured to use a TCP connection to access the + database server, not a UNIX domain socket. + +Load the dumped data (not forgetting to use the differing port number):: + + postgres_at_doozer:~> psql -p 5433 template1 -f SAVE + [...] + +Now the Apache server, and possibly other services +(:file:`/etc/mirrorbrain.conf`) need to be changed to the temporary port, and +(gracefully) restarted. + +.. note:: + Verify that everything works as expected with the temporary database. If it + does, stop the primary PostgreSQL server (and verify again that everything + still works). + +From here on, the next steps are probably obvious. You would proceed as +described in the previous section. After upgrading the PostgreSQL install, +loading the data, copying/merging :file:`postgresql.conf` and +:file:`pg_hba.conf`, you would revert the Apache configuration to use port 5432 +and reload it. + +If everything works, you can stop and remove the temporary database installation. + + +.. + additional steps on mirrordb: + cron stop on batavia510 + cron stop on mirrordb + repopusher stop + _______________________________________________ mirrorbrain-commits mailing list Archive: http://mirrorbrain.org/archive/mirrorbrain-commits/ Note: To remove yourself from this list, send a mail with the content unsubscribe to the address mirrorbrain-commits-request_at_mirrorbrain.orgReceived on Fri Aug 14 2009 - 10:35:25 GMT
This archive was generated by hypermail 2.3.0 : Mon Feb 20 2012 - 23:47:04 GMT