stompro/database_backup_restore.adoc

## database_backup_restore.adoc

      
            Raw
  

              database_backup_restore.adoc
            
          
Evergreen Database - Backup and Restore

Tips and tricks on backing up and restoring your Evergreen Database.


Backup to directory format dump.  This allows the dump to be done in parrallel for speed, and automatically compresses the output.  The downside is that it may not be possible to restore to a different version of postgresql.  If you need to move from one version of postgres to another, don’t use the directory format.
pg_dump -U evergreen -j 4 -F d -f $BACKUPDIR/$DBNAME-$CURTIME $DBNAME


Restore into a new database
If you are setting up a copy of your production database on the same or a different server you may want to restore to a different database name.


Create DB to restore into;
createdb egdbdev-5-3-2016 -E UTF8 -O evergreen -T template0


Restore into your new DB
pg_restore -v -Fd -d egdbdev-5-3-2016 -j 4  egdbprod-2016-05-03-13-37-01


Connect to DB and set the search path.
SELECT evergreen.change_db_setting('search_path', ARRAY['evergreen','public','pg_catalog']);


Proceed with database upgrade


Use eg_db_config to change which database is used by your install.


## notes.txt
I've imported evergreen in ampty evergreen database with commands "pg_dump -Fc -a evergreen > evergreen.dump" from old database to new with "pg_restore --disable-triggers -d evergreen evergreen.dump"
<mixo> what is downside of this method
* Shae (~chatzilla@cpe-174-99-59-211.nc.res.rr.com) has joined #evergreen
<Stompro> mixo, I believe that you also need to set the schema search path once you restored the data... let me find info.
<Stompro> mixo, see http://goo.gl/pp45hH, line 16 needs to be re-run once you have restored since that just sets a database setting, it isn't part of the schema.
<mixo> thank you
<Stompro> I haven't done a restore yet, so I don't have any other tips.  I think we should have a chapter in the documentation, I'll add it to the todo list.
<Stompro> I mean, I think a chapter for backups and restores should be added to the docs, not that I think there is one now.
<Dyrcona> I do similar restores "all the time" an resetting the path is about the only gotcha.
<Dyrcona> s/an/and/
<Dyrcona> You will sometimes see errors in the restore if your versions of postgres are different.
<Dyrcona> What I typically see going from 9.1 to 9.3 are about 27 messages about not being able to lock resources, but they are harmless.
<tsbere> For note: Resetting the path is only a gotcha if you aren't doing an *entire server* restore. We do single database restores so it is an issue.


<Stompro> jeffdavis, thanks, I saw someone mention using it once, I'm just wondering if it speeds up a restore.  I'm wondering if a reingest happens during restore when triggers are not disabled?
<jeffdavis> pg_dump -Fd -Z 9 --serializable-deferrable -f dumpfile production_evergreen_db ; psql -vdb_name=new_db -f create_database_extensions.sql ; pg_restore -Fd -d new_db dumpfile
<jeffdavis> is basically our process
<jeffdavis> restore is fairly slow (I run it overnight) but no, I don't believe it does a reingest
<jeffdavis> maybe it would if your dumpfile is in SQL format (pg_dump -Fp or without the -F option)
<Stompro> I'm using the directory format also, with -j 4 to speed things up.
<Stompro> I'm not using --serializable-deferrable, I'll have to read up on that.
<Stompro> Hmmm, I guess I should be using that.
<bshum> pg_restore's -j option if you have more CPUs is quite nice.
<bshum> I don't recall trying out parallel pg_dump though
<bshum> Which sounds like it's an option in PG 9.3+
<bshum> An old article, but seems to indicate that it exists: http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-parallel-pg_dump/
<jeff> bshum: yeah, it's handy. :-) works with directory-format backups only.
<jeff> (as Stompro described)
<jeff> Stompro: keep in mind that the --disable-triggers option is only relevant for data only dumps or restores.
<jeff> Stompro: otherwise, pg_restore's going to copy the data into the tables before it adds things like constraints and triggers.
	I've imported evergreen in ampty evergreen database with commands "pg_dump -Fc -a evergreen > evergreen.dump" from old database to new with "pg_restore --disable-triggers -d evergreen evergreen.dump"
	<mixo> what is downside of this method
	* Shae (~chatzilla@cpe-174-99-59-211.nc.res.rr.com) has joined #evergreen
	<Stompro> mixo, I believe that you also need to set the schema search path once you restored the data... let me find info.
	<Stompro> mixo, see http://goo.gl/pp45hH, line 16 needs to be re-run once you have restored since that just sets a database setting, it isn't part of the schema.
	<mixo> thank you
	<Stompro> I haven't done a restore yet, so I don't have any other tips. I think we should have a chapter in the documentation, I'll add it to the todo list.
	<Stompro> I mean, I think a chapter for backups and restores should be added to the docs, not that I think there is one now.
	<Dyrcona> I do similar restores "all the time" an resetting the path is about the only gotcha.
	<Dyrcona> s/an/and/
	<Dyrcona> You will sometimes see errors in the restore if your versions of postgres are different.
	<Dyrcona> What I typically see going from 9.1 to 9.3 are about 27 messages about not being able to lock resources, but they are harmless.
	<tsbere> For note: Resetting the path is only a gotcha if you aren't doing an entire server restore. We do single database restores so it is an issue.


	<Stompro> jeffdavis, thanks, I saw someone mention using it once, I'm just wondering if it speeds up a restore. I'm wondering if a reingest happens during restore when triggers are not disabled?
	<jeffdavis> pg_dump -Fd -Z 9 --serializable-deferrable -f dumpfile production_evergreen_db ; psql -vdb_name=new_db -f create_database_extensions.sql ; pg_restore -Fd -d new_db dumpfile
	<jeffdavis> is basically our process
	<jeffdavis> restore is fairly slow (I run it overnight) but no, I don't believe it does a reingest
	<jeffdavis> maybe it would if your dumpfile is in SQL format (pg_dump -Fp or without the -F option)
	<Stompro> I'm using the directory format also, with -j 4 to speed things up.
	<Stompro> I'm not using --serializable-deferrable, I'll have to read up on that.
	<Stompro> Hmmm, I guess I should be using that.
	<bshum> pg_restore's -j option if you have more CPUs is quite nice.
	<bshum> I don't recall trying out parallel pg_dump though
	<bshum> Which sounds like it's an option in PG 9.3+
	<bshum> An old article, but seems to indicate that it exists: http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-parallel-pg_dump/
	<jeff> bshum: yeah, it's handy. :-) works with directory-format backups only.
	<jeff> (as Stompro described)
	<jeff> Stompro: keep in mind that the --disable-triggers option is only relevant for data only dumps or restores.
	<jeff> Stompro: otherwise, pg_restore's going to copy the data into the tables before it adds things like constraints and triggers.