Problem doing pg_dumpall

From: alvaro(at)audifarma(dot)com(dot)co
To: pgsql-admin(at)postgresql(dot)org
Subject: Problem doing pg_dumpall
Date: 2004-04-15 22:06:23
Message-ID: 37411.200.31.204.249.1082066783.squirrel@audifarm.audifarma.com.co
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

As usual I wrote this email because I'm getting problems with posgtresql,
this time is with the pg_dumpall command, here´s the story:

We always make a copy of our database running

root(at)realserver#/var/lib/pgsql/bin/pg_dumpall > backup

It always worked fine but lately when I run this command It stops in the
middle of the process... because of the size of the backup file 2.5Gb
against 6Gb when doing it normally.

I looked at the tail of the backup file (#tail -100 backup | less)

And all I see is:

###########################################################################

-- TOC entry 442 (OID 18078)
-- Name: TABLE tbl_fact_sucursales; Type: COMMENT; Schema: public; Owner:
postgres
--

COMMENT ON TABLE tbl_fact_sucursales IS 'Archivo Plano de SIIGO, campo
sucursal';

--
-- TOC entry 457 (OID 18111)
-- Name: TABLE tbl_pg_errores_clases; Type: COMMENT; Schema: public;
Owner: postgres
--

COMMENT ON TABLE tbl_pg_errores_clases IS 'Tabla de clasificación de
errores reportados por postgresql, tomado del Apéndice A
.Error Codes de la docuemntación oficial de PostgreSQL ver 7.4.1';

\connect template1

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

SET SESSION AUTHORIZATION 'postgres';

SET search_path = public, pg_catalog;

--
-- TOC entry 7 (OID 31515354)
-- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema:
public; Owner: postgres
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;

SET SESSION AUTHORIZATION DEFAULT;

--
-- TOC entry 6 (OID 31515355)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
--

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;

SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 5 (OID 2200)
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;

SET SESSION AUTHORIZATION DEFAULT;

--
-- TOC entry 3 (OID 1)
-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON DATABASE template1 IS 'Default template database';

SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 4 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';

###########################################################################

The first two tables are part of our database the rest as I understand
are part of the database template1 which must exist in order to create
other datbases when need it.

I've checked at the running database using pgAdmin III and everything
apperars normal there are three DB mydatabase, template0 and template1,
the user postgres also exists and has proper rights.

Does anyone know what the problem is...? any ideas...?

PLAN B

By now I run /var/lib/pgsql/bin/pg_dump -d mydatabase > backup to make
backup copies, but when I recover that copy in other machine the
constrains and indexes dissaper, I follow this steps:

root(at)anothermachine#su postgres
$createdb mydatabase
$psql -d dbsa < backup

Do you think that I have to recover the schema and then all the data...?

I know that maybe this not a big deal because in the worst case I can
create the Indexes and constraints manually, but I really want to know why
is that? Am I donig things wrong..?

I hope you can help me...

Thanks for taking some of your time to look at this message.

Bye

Alvaro Arcila
Pereira, Colombia (S.A.)

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2004-04-15 23:33:41 Re: Maximum Number Tables
Previous Message Peter Eisentraut 2004-04-15 18:26:59 Re: SSL client configuration ...