Re: backup and restore questions

On Thursday 19 February 2004 17:41, Sally Sally wrote:
> I had a few questions
> concerning the backup/restore process for pg.

Please don't post HTML if you can avoid it.

> class=RTE> </DIV>
> <DIV class=RTE>1) Is it possible to dump data onto an existing
> database that contains data (assumning the schema of both are the same).
> Has anyone done this? I am thinking of this in order to expediate the data
> load process</DIV>

No reason why you can't copy the data in - assuming primary keys don't clash
of course.

> <DIV class=RTE>2) I read that when dumping and restoring data the insert
> option is safer but slower than copy? Does anyone know from experience how
> much slower (especially for a database containing millions of
> records).</DIV> <DIV class=RTE> </DIV>

It's not safer so much as more standard - any database can handle a series of
INSERT statements. Inserts are a lot slower.

> <DIV class=RTE>3) can pg_restore accept a file that is not archived like a
> zipped file or plain text file (file.gz or file)</DIV> <DIV

You can't restore a whole database. It's straightforward enough to use COPY or
\copy with psql to handle a simple tab (or whatever) separated file though.
If you want to unzip the file first, well, that's what scripting is for.

> <DIV class=RTE>4) Is the general practise to have one whole dump
> of a database or several separate dumps (by table etc...)?

Personally, I dump the data+schema in one file and the schema (again) in
another file. Makes it easy to scan through the schema. You can restore just
a single table from a full dump anyway - see the manuals for full details.

--
  Richard Huxton
  Archonet Ltd