Thread: Is it possible to "pip" pg_dump output into new db ?
Hi,
we are currently in the process of upgrading a production/live 1 TB database from 9.2 to 9.3 via pg_dump, which is quite a lengthy process.
Fortunately we have a capable spare-server so we can restore into a clean, freshly setup machine.
I just wondered wether the intermediate step of writing the dump-file and re-reading it to have it written to the database is really necessary. Is there any way to "pipe" the dump-file directly into the new database-process or would such functionality make sense ?
I can only speak for us, but each time we do a dump/restore we need to extract/copy/move very large files and piping directly into something like psql/pg_restore on another machine etc. would greatly reduce upgrade-time/pain.
Thanks and best regards,
Frank
On 25/03/2014 13:56, Frank Foerster wrote: > > Hi, > > we are currently in the process of upgrading a production/live 1 TB > database from 9.2 to 9.3 via pg_dump, which is quite a lengthy process. > > Fortunately we have a capable spare-server so we can restore into a > clean, freshly setup machine. > > I just wondered wether the intermediate step of writing the dump-file > and re-reading it to have it written to the database is really > necessary. Is there any way to "pipe" the dump-file directly into the > new database-process or would such functionality make sense ? Surely: pg_dump [...etc...] | psql [...etc...] Though I'm sure it will still take a long time for a database of that size. Another option to explore would be to use Slony, which can replicate databases between different Postgres versions - one of its design use-cases is to perform upgrades like this with a minimum of down-time. You can replicate the database over to the new server, and then switching need take only seconds once the new one is ready. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Tuesday, March 25, 2014 02:56:48 PM Frank Foerster wrote: > Hi, > > we are currently in the process of upgrading a production/live 1 TB > database from 9.2 to 9.3 via pg_dump, which is quite a lengthy process. > > Fortunately we have a capable spare-server so we can restore into a clean, > freshly setup machine. > > I just wondered wether the intermediate step of writing the dump-file and > re-reading it to have it written to the database is really necessary. Is > there any way to "pipe" the dump-file directly into the new > database-process or would such functionality make sense ? > > I can only speak for us, but each time we do a dump/restore we need to > extract/copy/move very large files and piping directly into something like > psql/pg_restore on another machine etc. would greatly reduce > upgrade-time/pain. > > Thanks and best regards, > > Frank Sure. For maximum speed, something like: pg_dump [options] source_db | pigz - | ssh -e none user@target "gunzip - | psql [options] target_db" Depending on your hardware, though, doing a custom backup to a target file and then using it for a parallel restore would probably overall end up being faster, plus you get to keep the backup if needed. In my experience, the restore is a lot slower than the backup. Slony is also great, to save most of the downtime. At the expense of a lot of setup and testing time.
Ray, Alan,
thanks for your replies.
We have tested the dump/restore procedure with a few smaller databases and it worked fine. We had a few smaller hiccups with the large database as it required a few modules and special tablespaces before it would start actually copying data. But not a real problem, a test-upgrade is currently running.
We will use this upgrade-cycle to systematically test and evaluate all upgrade options.
We have an "if-all-else-fails"-full plain dump that we just restored testwise. In this case to see performance but we regularly restore it to make sure it actually works. The file is compressed with rar. Packed size is about 100 GB, unpacked about 1 TB. Uncompressing alone on a decent machine (256GB Ram, 12 Core) took about 1 day. Importing via psql took about 12 hours (fsync off, wal_segments adjusted, etc.).
Currently we are running the direct pg_dump / pg_restore upgrade from a slave that we just took off wal-replication. As it is progressing i am expecting something in the 12-15 hour range.
Finally we will try the pg_upgrade-option on a test-slave. I expect that to be quite fast as it more or less just needs to copy the data once and correct/adjust system tables, if i am not mistaken. So that should take about as long as it takes to copy 1 TB of data plus the table-adjustments.
Fortunately we can use the weekend to freeze the database so the reduced-downtime that might be achieved by the slony-approach is not a true requirement and we can avoid the complexities of that approach.
Thanks,
Frank
On Tue, Mar 25, 2014 at 4:46 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 25/03/2014 13:56, Frank Foerster wrote:Surely:
>
> Hi,
>
> we are currently in the process of upgrading a production/live 1 TB
> database from 9.2 to 9.3 via pg_dump, which is quite a lengthy process.
>
> Fortunately we have a capable spare-server so we can restore into a
> clean, freshly setup machine.
>
> I just wondered wether the intermediate step of writing the dump-file
> and re-reading it to have it written to the database is really
> necessary. Is there any way to "pipe" the dump-file directly into the
> new database-process or would such functionality make sense ?
pg_dump [...etc...] | psql [...etc...]
Though I'm sure it will still take a long time for a database of that size.
Another option to explore would be to use Slony, which can replicate
databases between different Postgres versions - one of its design
use-cases is to perform upgrades like this with a minimum of down-time.
You can replicate the database over to the new server, and then
switching need take only seconds once the new one is ready.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie