
If anybody out there is comfortable with using make, has the PG sources on
their machine, and is interested in testing a new version of pg_dump,
please let me know.

Details of the revised pg_dump, and new pg_restore are at the end of this
message.

Thanks,

Philip Warner.

-------------
The basic idea is to use pg_dump to dump an *entire* database, and then use
pg_restore to choose what is restored.

The salient features are as follows:

- pg_dump still used to dump database; all output is via new interface
(virtually all of the pg_dump code is changed, but not the logic). The
changes are relatively minor, all the same.

- the '-c' option is not used in pg-dump: it now dumps the commands to
delete the schema, and it is up to the user of pg_restore to decide if they
are output.

- the default output file format is stll text, but a new custom format 
with compressed sections (the data dumps) is available via the '-Fc' flag.
It is NOT a text file.

- pg_restore reads the backup file and, depending on the options chosen,
produces a script (to stdout) that can be sent to psql.

- by default pg_restore outputs the schema/data in the order it was sent
from pg_dump, but the --oid flag will send the output in order of
increasing OID, and the --rearrange flag will put all 'non-parental' (??)
items at the end, after the data. (eg. indexes, acls, triggers etc).
Needless to say that the best results com from using both of these options.

- If the -c (clear) option is chosen in pg_restore, it also dumps the
'drop' commands in reverse order at the start of the script. This *should*
make it more reliable than dumping them when the item is defined. It also
means that triggers can be dropped.

- The --toc option shows a summary of the restore operation that would be
performed if the --toc were not there.

Please send me an email if you are interested and have the time to test them.

Thanks,

Philip Warner.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sun, 02 Jul 2000 14:36:44 +1000
Philip Warner <pjw@rhyme.com.au>
Announce: Testers needed for revised pg_dump/pg_restore

This is also available via ftp from:

    ftp://ftp.rhyme.com.au/pub/pg_backup_110.tar.gz


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sun, 02 Jul 2000 15:36:35 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Announce: Testers needed for revised
  pg_dump/pg_restore

Hi!
I have a problem with vacuumdb on one of my tables.

(spiral:3)-[~]$vacuumdb -t osoby -v dziekanat
NOTICE:  --Relation osoby--
NOTICE:  Pages 229: Changed 0, reaped 16, Empty 0, New 0; Tup 4427: Vac 5,
Keep/VTL 0/0, Crash 0, UnUsed 70, MinLen 64, MaxLen
616; Re-using: Free/Avail. Space 18176/2288; EndEmpty/Avail. Pages
1/10. CPU 0.02s/0.00u sec.
NOTICE:  Index osoby_Imiďż˝_key: Pages 44; Tuples 4427: Deleted 0. CPU
0.01s/0.01u sec.
NOTICE:  Index osoby_pkey: Pages 15; Tuples 4427: Deleted 0. CPU
0.00s/0.00u sec.
NOTICE:  Rel osoby: Pages: 229 --> 228; Tuple(s) moved: 0. CPU 0.01s/0.00u
sec.
NOTICE:  FlushRelationBuffers(osoby, 228): block 223 is referenced
(private 0, global 1)
FATAL 1:  VACUUM (vc_repair_frag): FlushRelationBuffers returned -2
pqReadData() -- backend closed the channel unexpectedly.
	This probably means the backend terminated abnormally
	before or while processing the request.
connection to server was lost
vacuumdb: vacuum failed


this table is referenced in my db by a tree of FOREIGN KEYs. 

however my db seems to work and I am able to do pg_dump
Rescently, I dumped and restored it and for a few days I was able to
do vacuumdb. Today, the problem is here again.

any ideas?

thanks for any help.


(I use Postgres ver 7.0.2 on Debian Potato/ dual PII  box)


Sun, 2 Jul 2000 11:01:37 +0200 (CEST)
Marcin Inkielman <marn@wsisiz.edu.pl>
vacuumdb problem

It's probably best to look in the directory:

    ftp://ftp.rhyme.com.au/pub/

for the latest version.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sun, 02 Jul 2000 22:46:23 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Announce: Testers needed for revised
  pg_dump/pg_restore
Marcin Inkielman <marn@wsisiz.edu.pl> writes:
> NOTICE:  FlushRelationBuffers(osoby, 228): block 223 is referenced
> (private 0, global 1)
> FATAL 1:  VACUUM (vc_repair_frag): FlushRelationBuffers returned -2

> this table is referenced in my db by a tree of FOREIGN KEYs. 

Hmm, I wonder whether there is a buffer-refcount leak in the foreign
key stuff.

> however my db seems to work and I am able to do pg_dump
> Rescently, I dumped and restored it and for a few days I was able to
> do vacuumdb. Today, the problem is here again.

You will probably find that stopping and restarting the postmaster
will make the problem go away (until it comes back again).  Might
be an acceptable workaround to let you vacuum, until we can find
the bug.

Do you think you can extract a reproducible example?  Short of that,
it'd be helpful to at least see the declarations of "osoby" and all
associated tables.

			regards, tom lane

Sun, 02 Jul 2000 12:47:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: vacuumdb problem 
On Sun, 2 Jul 2000, Tom Lane wrote:

> Date: Sun, 02 Jul 2000 12:47:55 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Marcin Inkielman <marn@wsisiz.edu.pl>
> Cc: pgsql-general@postgresql.org, pgsql-hackers@postgresql.org
> Subject: Re: [GENERAL] vacuumdb problem 
> 
> Marcin Inkielman <marn@wsisiz.edu.pl> writes:
> > NOTICE:  FlushRelationBuffers(osoby, 228): block 223 is referenced
> > (private 0, global 1)
> > FATAL 1:  VACUUM (vc_repair_frag): FlushRelationBuffers returned -2
> 
> > this table is referenced in my db by a tree of FOREIGN KEYs. 
> 
> Hmm, I wonder whether there is a buffer-refcount leak in the foreign
> key stuff.
> 
> > however my db seems to work and I am able to do pg_dump
> > Rescently, I dumped and restored it and for a few days I was able to
> > do vacuumdb. Today, the problem is here again.
> 
> You will probably find that stopping and restarting the postmaster
> will make the problem go away (until it comes back again).  Might
> be an acceptable workaround to let you vacuum, until we can find
> the bug.

thanks, it worked.

> 
> Do you think you can extract a reproducible example?  Short of that,
> it'd be helpful to at least see the declarations of "osoby" and all
> associated tables.
> 
> 			regards, tom lane
> 

sure..

CREATE TABLE "osoby" (
	"Nazwisko" text NOT NULL,
	"Imiďż˝" text NOT NULL,
	"Drugie imiďż˝" text,
	"Data urodzenia" date NOT NULL,
	"Miejsce urodzenia" text,
	"Nazwisko rodowe" text,
	"Imiďż˝ ojca" text,
	"Imiďż˝ matki" text,
	"Nazwisko rodowe matki" text,
	"Pďż˝eďż˝" int2,
	"Stan cywilny" int2,
	"Numer dowodu osobistego" text,
	"PESEL" text,
	"NIP" text,
	"Nazwa szkoďż˝y" text,
	"Nr ďż˝wiadectwa" text,
	"Data wystawienia ďż˝wiad" date,
	"Miejsce wyst ďż˝waid" text,
	"ulica" text,
	"kod" text,
	"poczta" text,
	"miejscowoďż˝ďż˝" text,
	"gmina" text,
	"wojew" text,
	"telefon" text,
	"Adres koresp - ulica" text,
	"Adres koresp - kod" text,
	"Adres koresp - poczta" text,
	"Adres koresp - miejscowoďż˝ďż˝" text,
	"Adres koresp - gmina" text,
	"Adres koresp - wojew" text,
	"Adres koresp - telefon" text,
	"ob wojskowy" int2,
	"WKU" int4,
	"WKU - kod" text,
	"WKU - miejscowoďż˝ďż˝" text,
	"WKU - ulica" text,
	"osoba_id" int4 NOT NULL);


CREATE TABLE "teczki_t" (
	"osoba_id" int4 NOT NULL,
	"tr_wdz" int4 NOT NULL,
	"dyplom_id" int2 NOT NULL,
	"termin_egzaminu_id" int2 NOT NULL,
	"teczka_id" int4 NOT NULL,
	"pracownik_id" int4,
	"stan_id" int4,
	"data" datetime,
	"pracownik_id_r" int4,
	"data_r" datetime,
	"uwagi" text,
	"poz_kurs" int2,
	"kurs_komp" int2,
	FOREIGN KEY (osoba_id) REFERENCES "osoby"(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (dyplom_id) REFERENCES "Dyplom"(symbol) ON UPDATE CASCADE,
	FOREIGN KEY (stan_id) REFERENCES "stany"(stan_id) ON UPDATE CASCADE
	);


CREATE TABLE "studenci" (
	"numer_albumu" int4 NOT NULL,
	"osoba_id" int4 NOT NULL,
	"tr_wdz_id" int4 NOT NULL,
	"grupa_id" int4,
	"grupa_jez_id" int4,
	"semestr" int4 NOT NULL,
	"repeta" bool,
	"warunki" int4,
	"znacznik" int4,
	FOREIGN KEY (tr_wdz_id) REFERENCES tr_wdz(tr_wdz_id) ON DELETE RESTRICT ON UPDATE CASCADE,
	FOREIGN KEY (osoba_id) REFERENCES osoby(osoba_id) ON DELETE RESTRICT ON UPDATE CASCADE,
	FOREIGN KEY (grupa_id) REFERENCES grupy_stud(grupa_id) ON DELETE RESTRICT ON UPDATE CASCADE,
	FOREIGN KEY (grupa_jez_id) REFERENCES grupy_jez_stud(grupa_jez_id) ON DELETE RESTRICT ON UPDATE CASCADE
	);


CREATE TABLE "rejestr_decyzji" (
	"decyzja_id" int4 NOT NULL,
	"osoba_id" int4,
	"numer_albumu" int4,
	"data_pocz" date,
	"data_konca" date,
	"typ_decyzji_id" int4,
	"podjeta_przez" int4,
	"podjeta_dnia" datetime,
	"anulowana_przez" int4,
	"anulowana_dnia" datetime,
	"uwagi" text,
	FOREIGN KEY (osoba_id) REFERENCES "osoby"(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (numer_albumu) REFERENCES album(numer_albumu) ON DELETE CASCADE ON UPDATE CASCADE
	);

CREATE TABLE "konto" (
	"osoba_id" int4 NOT NULL,
	"login" text,
	"gr_id" int4,
	"alias" text,
	"pass" text,
	"homedir" text,
	"ma_konto" bool,
	FOREIGN KEY (osoba_id) REFERENCES "osoby"(osoba_id) ON DELETE CASCADE ON UPDATE CASCADE
	);

/*only 2 indexes on osoby*/

CREATE UNIQUE INDEX "osoby_pkey" on "osoby" using btree ("osoba_id" "int4_ops" );
CREATE UNIQUE INDEX "osoby_Imiďż˝_key" on "osoby" using btree (
"Imiďż˝" "text_ops", "Nazwisko" "text_ops", "Data urodzenia" "date_ops" );


almost all this tables are referenced by other tables (however this
references are not cyclic). the db is not very large from the point of
view of data but it contains >200 tables.

I hope it will help a little...

      regards

-- 
mi


Sun, 2 Jul 2000 22:23:02 +0200 (CEST)
Marcin Inkielman <marn@wsisiz.edu.pl>
Re: [GENERAL] vacuumdb problem 