backup and restore questions

Dear Sally Sally ,

I had a few questions concerning the backup/restore process for pg.

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

This can work provided the database are on same system and have have
same schema not tried though.

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

If you are real serious about your data best way AFAIK is insert
because with insert statments you can move around in case
you upgrade your database or add a new colum in new table but trying to
restore a old data of the same table.
On an
Celeron 900
PostgreSQL 7.3.4
RH 9.0
a 151Kb tared backup takes about 5 Minutes.
Though data restore depends 99 % on disk throughput 1% on CPU in case
of plain insert file
and 90 % on disk throughput and 10 % CPU in case of tared file.

3) can pg_restore accept a file that is not archived like a zipped
file or plain text file (file.gz or file)

Can use both zipped and Plain. New versions of pg_restore i.e 7.3 >
identify the file format automatically

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

One dump for data and other dump for schema will always help.

--
Regards,
Vishal Kashyap
Director / Lead Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com
~*~*~*~*~*~*~*
You Guys start coding I will take care of what this
customers needs.
~*~*~*~*~*~*~*
I Know you believe my words so logon to Jabber.org
and add vishalkashyap@jabber.org to your roster.
OR
Seek Me at 264360076
~*~*~*~*~*~*~*
I am usually called as Vishal Kashyap
but my Girlfriend calls me as Vishal CASH UP.
This is because others identify me because of my
generosity but my Girlfriend identify me because
of my CASH.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*

On Thu, 19 Feb 2004, Sally Sally wrote:

I had a few questions concerning the backup/restore process for pg.
�
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

I do it all the time. Note that if you have constraints that adding the
new data would violate, it's likely to not import anything.

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

Depends, but usually about twice as slow to as much as ten times slower.
It isn't really any "safer" just more portable to other databases.

3) can pg_restore accept a file that is not archived like a zipped file or plain text file (file.gz
or file)

yes, plain text is fine. to do a .gz file you might have to do a gunzip
first. I usually just stick to plain text.

4) Is the general practise to have one�whole�dump of a database or several separate dumps (by table
etc...)?

It's normal to see a single large dump. Where I work we run >80 databases
(running on 7.2.x so no schemas) with each database belonging to a
particular application. I wrote a custom wrapper for pg_dump that acts
something like pg_dumpall but dumps each database to a seperate file.
Makes restoring one table or something like that for a single database
much easier when you don't have to slog though gigabytes of unrelated
data.

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>&nbsp;</DIV>
<DIV class=RTE>1) Is it possible to dump&nbsp;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>&nbsp;</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&nbsp;whole&nbsp;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