|
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. |