Thread: One DB not backed up by pg_dumpall
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).
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
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.
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.
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.
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 ;)
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.
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 ;)