Backup-PostgreSQL - Revision history

Makopoppo: Redirecting to Backups#PostgreSQL

2011-05-04T10:35:15Z

Redirecting to Backups#PostgreSQL

Makopoppo: integrated

2011-05-04T10:33:00Z

integrated

Makopoppo: Replacing page with '{{delete|This page is merged into Backups}}'

2011-01-03T02:55:28Z

Replacing page with '{{delete|This page is merged into Backups}}'

Fritigern at 15:26, 19 November 2010

2010-11-19T15:26:17Z

Brentseidel: PostgreSQL backup instructions.

2009-07-10T18:06:37Z

PostgreSQL backup instructions.

New page

I recently had to do a backup and restore of my PostgreSQL database. Here, for the benefit of the other PostgreSQL user are my notes. I did my backup and restore because I was updating the database software.
* Shutdown OpenSim
* Use the pg_dump command to backup the database.
** In my case, I did this as the postgres user. It should work as the OpenSim (or whatever user owns the OpenSim database) user.
** pg_dump -b -Fc -C -f opensim.db.backup opensim
** The -b option requests that large objects are included in the dump. This would be things like textures and sounds.
** The -Fc option creates the backup file as a gzipped tar file
** The -C option includes commands to create the database when restoring
** The -f option specifies the name of the output file. In this case opensim.db.backup
** There are a number of other options that can be used. For example, if you're doing the backup on a different machine, the -h option allows you to specify the machine that the database is running on.
* Copy the opensim.db.backup file to a CD-ROM or wherever you want it saved.
* Do what you need to do. In my case, it was upgrading the PostgreSQL software
* While PostgreSQL was shutdown, I saved a tar of its /data directory to make it easier to recover if the upgrade went bad.
* I also took copies of the configuration files in the /data directory so that I didn't have to recreate them.
* Delete the /data directory
* Initialize the new database
** The command is initdb -D <path to /data directory>
* Start the new PostgreSQL software (it will complain about data formats if you try this before you delete the old /data and run initdb)
* Create the opensim user
* Restore the data using pg_restore
** pg_restore -C -d template1 opensim.db.backup
** Read the PostgreSQL documentation for more information.
In my case, it seemed to work fine. After I started up OpenSim, I logged in and found everything as it should be. I would really recommend trying this first on a test Sim that you didn't care about. Also study the documentation before trying this. Your setup may be different than mine.

← Older revision		Revision as of 10:35, 4 May 2011
Line 1:		Line 1:
−	#Redirect [[~~Backup~~#PostgreSQL]]	+	#Redirect [[Backups#PostgreSQL]]

← Older revision		Revision as of 10:33, 4 May 2011
Line 1:		Line 1:
−	~~{{delete\|This page is merged into~~ [[~~Backups~~]]}}	+	#Redirect [[Backup#PostgreSQL]]

← Older revision		Revision as of 15:26, 19 November 2010
Line 22:		Line 22:
	** Read the PostgreSQL documentation for more information.		** Read the PostgreSQL documentation for more information.
	In my case, it seemed to work fine. After I started up OpenSim, I logged in and found everything as it should be. I would really recommend trying this first on a test Sim that you didn't care about. Also study the documentation before trying this. Your setup may be different than mine.		In my case, it seemed to work fine. After I started up OpenSim, I logged in and found everything as it should be. I would really recommend trying this first on a test Sim that you didn't care about. Also study the documentation before trying this. Your setup may be different than mine.
		+
		+
		+	[[Category:Database]]

@@ Line 1: / Line 1: @@
-I recently had to do a backup and restore of my PostgreSQL database.  Here, for the benefit of the other PostgreSQL user are my notes.  I did my backup and restore because I was updating the database software.
+{{delete|This page is merged into [[Backups]]}}
-* Shutdown OpenSim
-* Use the pg_dump command to backup the database.
-** In my case, I did this as the postgres user.  It should work as the OpenSim (or whatever user owns the OpenSim database) user.
-** pg_dump -b -Fc -C -f opensim.db.backup opensim
-** The -b option requests that large objects are included in the dump.  This would be things like textures and sounds.
-** The -Fc option creates the backup file as a gzipped tar file
-** The -C option includes commands to create the database when restoring
-** The -f option specifies the name of the output file.  In this case opensim.db.backup
-** There are a number of other options that can be used.  For example, if you're doing the backup on a different machine, the -h option allows you to specify the machine that the database is running on.
-* Copy the opensim.db.backup file to a CD-ROM or wherever you want it saved.
-* Do what you need to do.  In my case, it was upgrading the PostgreSQL software
-* While PostgreSQL was shutdown, I saved a tar of its /data directory to make it easier to recover if the upgrade went bad.
-* I also took copies of the configuration files in the /data directory so that I didn't have to recreate them.
-* Delete the /data directory
-* Initialize the new database
-** The command is initdb -D <path to /data directory>
-* Start the new PostgreSQL software (it will complain about data formats if you try this before you delete the old /data and run initdb)
-* Create the opensim user
-* Restore the data using pg_restore
-** pg_restore -C -d template1 opensim.db.backup
-** Read the PostgreSQL documentation for more information.
-In my case, it seemed to work fine.  After I started up OpenSim, I logged in and found everything as it should be.  I would really recommend trying this first on a test Sim that you didn't care about.  Also study the documentation before trying this.  Your setup may be different than mine.
-−
-−
-[[Category:Database]]