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?

12/19/05, Francisco Reyes <lists (AT) stringsutils (DOT) comwrote:
Michael Fuhr writes:

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?

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 PGPASSWRD <password>
/usr/local/bin/pg_dumpall |/usr/bin/bzip2 -c ><file>

Where <fileis
/
--
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.

12/20/05, Francisco Reyes <lists (AT) stringsutils (DOT) comwrote:
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;
inti;

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

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

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.