Re: Question on database backup

Hi,

I think this is most surprising behavior -- shouldn't the UPDATE raise
an error?

alvherre=# create table foo (a int[]);
CREATE TABLE
alvherre=# insert into foo values (null);
INSERT 33649 1
alvherre=# update foo set a[3] = '42';
UPDATE 1
alvherre=# select a, a is null from foo;
a | ?column?
---+----------
| t
(1 fila)
alvherre=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 7.5devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (Mandrake Linux 9.2 3.3.1-2mdk)
(1 fila)

(This is CVS tip as of a couple weeks ago)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El destino baraja y nosotros jugamos" (A. Schopenhauer)

Alvaro Herrera wrote:
> I think this is most surprising behavior -- shouldn't the UPDATE raise
> an error?

Surprising, but not new (probably has been there back to the Berkley
code), and has come up before on one of the lists (I think it might even
have been pgsql-bugs).

regression=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)
(1 row)

regression=# create table foo (a int[]);
CREATE TABLE
regression=# insert into foo values (null);
INSERT 1104092 1
regression=# update foo set a[3] = '42';
UPDATE 1
regression=# select a, a is null from foo;
a | ?column?
---+----------
| t
(1 row)

I'm still hoping to scrounge up the time to continue working on arrays
for 7.5, including figuring out how to deal with this.

Joe

We have customers who prefer to use their backup facilities
instead of what we provide in the app (we use pg_dump)
I hear speed is at least one consideration.

The questions I need to answer are these:

1) Is this absolutely safe to do file copy (cpio, or
smth. else, whatever the robust backup app. would use)
on the Postgres db, when it's completely shut down.

2) Same question, but the database is up and running in
read-only mode. We're making sure that no updates are
taking place.

If it matters - this is on Solaris, HP, Linux.
We're using v.7.3.2 running on UDS and v.7.3.4 on TCP/IP
We provide no explicit settings for wal, fsync and the like.
And (yes, I know) they often install it on NFS.

Thank you much.
Mike.

On Wed, 4 Feb 2004, Michael Brusser wrote:

> We have customers who prefer to use their backup facilities
> instead of what we provide in the app (we use pg_dump)
> I hear speed is at least one consideration.
>
> The questions I need to answer are these:
>
> 1) Is this absolutely safe to do file copy (cpio, or
> smth. else, whatever the robust backup app. would use)
> on the Postgres db, when it's completely shut down.

Yes, it is.

> 2) Same question, but the database is up and running in
> read-only mode. We're making sure that no updates are
> taking place.

Most likely, it is. No guarantees if the database is up and running, even
if you're certain there are no updates happening.

Also, you can use a snapshotting file system to make a backup image and
then back up the image, while the database is up and being accessed, both
by readers and writers, assuming you get REAL snapshots.

> If it matters - this is on Solaris, HP, Linux.
> We're using v.7.3.2 running on UDS and v.7.3.4 on TCP/IP
> We provide no explicit settings for wal, fsync and the like.
> And (yes, I know) they often install it on NFS.

OK, here's a couple more issues to chew on as well. Suppose you have a
backup, and the database server was compiled with UNKNOWN switches. The
machine burns to the ground. now you get to try and figure out how to
compile the database on the new server so it can read the old dataset.
This may or may not be a complete friggin' nightmare for you. Dumps can
move between versions / hardware configs / differently compiled versions
of postgresql with some amount of reliability. binary copies, may or may
not move so easily.

Scenario II, the HP burns to the ground, and your boss just buys a big old
intel box. how do you get your data up and running with a binary backup?
you don't.

Scenario III. Subtle corruption gets into your dataset due to a bad block
or what not. No one notices for a while. Suddenly, someone notices.
With only file system backups, with no error messages in them, how do you
determine when the corruption occurred and get the uncorrupt data out
leaving the corrupted behind?

Plain and simple. Postgresql is designed to be backed up by pg_dump.
Using anything else "isn't supported" so to speak, and may cause you
untold grief in the future. That said, sometimes file backups are the
perfect solution, just go into with your eyes open to the possible
problems, and I'd make a pg_dump every so often just in case.

On Wed, Feb 04, 2004 at 11:06:29AM -0800, Joe Conway wrote:
> Alvaro Herrera wrote:
> >I think this is most surprising behavior -- shouldn't the UPDATE raise
> >an error?
>
> Surprising, but not new (probably has been there back to the Berkley
> code), and has come up before on one of the lists (I think it might even
> have been pgsql-bugs).

Too bad :-(

> I'm still hoping to scrounge up the time to continue working on arrays
> for 7.5, including figuring out how to deal with this.

I have the same hope, though I know for sure that I won't have any time
to work on anything until March, and then I will probably devote most of
my time to Pg. When is beta freeze supposed to happen?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> When is beta freeze supposed to happen?

AFAIK, no date has been set at all. I doubt we'll even think about it
until we see how the Windows port effort goes.

regards, tom lane