Any reason why a database would not get dumped by pg_dumpall?
Always run pg_dumpall as the superuser.

I do a nightly dump and have checked several days so far and the database is
missing in all so far. :-(

The only thing, for that DB, that got backed up was the database, but not a
single table. So far from what I see other DBs were restored ok (At least
the ones I have checked so far).

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Sun, Dec 18, 2005 at 11:29:13PM -0500, Francisco Reyes wrote:
> Any reason why a database would not get dumped by pg_dumpall?
> Always run pg_dumpall as the superuser.

As the operating system superuser or as a database superuser?
There's a difference.

> I do a nightly dump and have checked several days so far and the database
> is missing in all so far. :-(

Is this a new procedure that has never worked, or is it an old
procedure with a new problem?

> The only thing, for that DB, that got backed up was the database, but not a
> single table. So far from what I see other DBs were restored ok (At least
> the ones I have checked so far).

What are the exact commands you're using to dump and restore? Have
you examined the output and the server's logs for errors and warnings?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Fuhr writes:

> On Sun, Dec 18, 2005 at 11:29:13PM -0500, Francisco Reyes wrote:
>> Any reason why a database would not get dumped by pg_dumpall?
>> Always run pg_dumpall as the superuser.
>
> As the operating system superuser or as a database superuser?
> There's a difference.

As the database superuser.

> Is this a new procedure that has never worked, or is it an old
> procedure with a new problem?

Old procedure with a new problem.

> What are the exact commands you're using to dump and restore? Have
> you examined the output and the server's logs for errors and warnings?


The nightly script is:
#!/bin/csh
setenv PGUSER pgsql
setenv PGPASSWORD <password>
/usr/local/bin/pg_dumpall |/usr/bin/bzip2 -c ><file>

Where <file> is
/vol1/backs/pgsql/dump_all.sql.bz2


This procedure has been running for a while.
Last night before upgrading from 8.0.x to 8.1 I ran the script, then
proceeded to upgrade. So far from what I can tell only lost that one
database. :-(

However it deeply worries me. I will need to find if the script above is the
problem or something else. In coming days will keep an eye on the dump. This
one db I lost was bad to loose, but not critical (personal wiki), however it
would have been horrible if had lost other databases.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Michael Fuhr writes:

> On Sun, Dec 18, 2005 at 11:29:13PM -0500, Francisco Reyes wrote:
>> Any reason why a database would not get dumped by pg_dumpall?
>> Always run pg_dumpall as the superuser.


Researched what was lost. It seems that all databases after a particular
database, called test, were not backed up.

Again almost out of pure luck, none of those databases were critical, but I
will need to research why this happened and more importantly how to make
sure pg_dumpall actually backs up all databases.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Michael Fuhr writes:

> On Sun, Dec 18, 2005 at 11:29:13PM -0500, Francisco Reyes wrote:
>> Any reason why a database would not get dumped by pg_dumpall?

Is there a way to check the successfull completion of pg_dumpall.
Loosing 3 databases is not an experience I want to repeat.

Perphaps it returns a value on failure?
Just checked the man page and did not see any reference to that regard.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 12/19/05, Francisco Reyes <lists@stringsutils.com> wrote:
> Michael Fuhr writes:
>
> > On Sun, Dec 18, 2005 at 11:29:13PM -0500, Francisco Reyes wrote:
> >> Any reason why a database would not get dumped by pg_dumpall?
> >> Always run pg_dumpall as the superuser.
> >
> > As the operating system superuser or as a database superuser?
> > There's a difference.
>
> As the database superuser.
>
> > Is this a new procedure that has never worked, or is it an old
> > procedure with a new problem?
>
> Old procedure with a new problem.
>
> > What are the exact commands you're using to dump and restore? Have
> > you examined the output and the server's logs for errors and warnings?
>
>
> The nightly script is:
> #!/bin/csh
> setenv PGUSER pgsql
> setenv PGPASSWORD <password>
> /usr/local/bin/pg_dumpall |/usr/bin/bzip2 -c ><file>
>
> Where <file> is
> /vol1/backs/pgsql/dump_all.sql.bz2
>
>
> This procedure has been running for a while.
> Last night before upgrading from 8.0.x to 8.1 I ran the script, then
> proceeded to upgrade. So far from what I can tell only lost that one
> database. :-(
>

- you still have the server where these databases exists?
- what version of pgsql, is this?

pg_dumpall ignore all databases with datallowconn = true, maybe it is the case?

> However it deeply worries me. I will need to find if the script above is the
> problem or something else. In coming days will keep an eye on the dump. This
> one db I lost was bad to loose, but not critical (personal wiki), however it
> would have been horrible if had lost other databases.
>

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jaime Casanova writes:

> - you still have the server where these databases exists?

No. I lost 3 databases.

> - what version of pgsql, is this?

It was 8.0.4
I was upgrading to 8.1.
I checked the nightly jobs had been running, then ran a manual one and
proceeded to do the upgrade.

> pg_dumpall ignore all databases with datallowconn = true, maybe it is the case?

The original database is gone so can't check that.

Do you know if there is a way to find out if pg_dumpall had problems?
Later today I plan to do a mini test.. run pg_dumpall as a user with rights
to only some tables and see if the program returns an error or if returns a
value upon failure... so I can modify my script.

It would be helpfull if the docs/man page were updated to indicate any info
about what pg_dumpall does in case of failures.

I am also planning on writing one or more scripts to check the pg_dumpall
file. My DBs are small enough that I can run a check on them (ie count how
many DBs were backed up, compare to how many "\connect" the dump file has).
Hopefully will make them semi-generic so others can re-use them too.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On 12/20/05, Francisco Reyes <lists@stringsutils.com> wrote:
> Jaime Casanova writes:
>
> > - you still have the server where these databases exists?
>
> No. I lost 3 databases.
>
> > - what version of pgsql, is this?
>
> It was 8.0.4
> I was upgrading to 8.1.
> I checked the nightly jobs had been running, then ran a manual one and
> proceeded to do the upgrade.
>

mmm... so at least you lost another database we can't check the
problem... too bad

> > pg_dumpall ignore all databases with datallowconn = true, maybe it is the case?
>
> The original database is gone so can't check that.
>
> Do you know if there is a way to find out if pg_dumpall had problems?
> Later today I plan to do a mini test.. run pg_dumpall as a user with rights
> to only some tables and see if the program returns an error or if returns a
> value upon failure... so I can modify my script.
>

i haven't tried but it seems that it exits...

/*
* Dump contents of databases.
*/
static void
dumpDatabases(PGconn *conn)
{
PGresult *res;
int i;

if (server_version >= 70100)
res = executeQuery(conn, "SELECT datname FROM pg_database WHERE
datallowconn ORDER BY 1");
else
res = executeQuery(conn, "SELECT datname FROM pg_database ORDER BY 1");

for (i = 0; i < PQntuples(res); i++)
{
int ret;

char *dbname = PQgetvalue(res, i, 0);

if (verbose)
fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname);

printf("\\connect %s\n\n", fmtId(dbname));
ret = runPgDump(dbname);
if (ret != 0)
{
fprintf(stderr, _("%s: pg_dump failed on database \"%s\",
exiting\n"), progname, dbname);
exit(1);
^^^^^^^^
}
}

PQclear(res);
}

> It would be helpfull if the docs/man page were updated to indicate any info
> about what pg_dumpall does in case of failures.
>
> I am also planning on writing one or more scripts to check the pg_dumpall
> file. My DBs are small enough that I can run a check on them (ie count how
> many DBs were backed up, compare to how many "\connect" the dump file has).
> Hopefully will make them semi-generic so others can re-use them too.
>


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match