Re: Schema-only dump dumps no constraints, no triggers

Hi, again,

I'm sorry about the lack of version information - I concentrated so much on describing the problem correctly, that I
forgotto provide basic information. The version at locations is 9.1.4 (though this is irrelevant now), the server is
9.0.4.

We found what the problem was. Another problem stems from it, however. Please read on.

To add to the information already provided - we have a two-way backup of the template database. One is a WAL
replicationand the other is londiste (skytools) replication with periodic complete copy. As it turned out, the "stable"
scriptuses not, as we remembered, the actual template database but the londiste-replicated database which was to make
nextcomplete copy a few days ago. The copy did not complete, however - the schema-table-column structure transfer
completed,but the constraints and triggers did not get through somehow, as there was a lack of hard drive space.
Diggingon it, we found out that the drive's space was not used up by files in the filesystem, it was filled with
deletedfiles that postgresql server was still clinging on to, probably for a good while. After restarting the server
many,many gigabytes were suddenly made available on disk. And this is the new problem - the server has quite a
throughputand this is probably what causes the "leakage". How can we force the server to let go of the files? Or maybe
itis an actual leak that needs to be studied upon? 

On a side note, obviously, the Windows dump came out alright because it was from the proper database, not the
replicatedcopy. 

Best regards,
Marek Kielar


Dnia 27 lipca 2012 4:46 Adrian Klaver <adrian.klaver@gmail.com> napisał(a):

> On 07/26/2012 04:09 PM, Marek Kielar wrote:
> > Hi,
> >
> > we are using "pg_dump -s" (schema-only) to copy the structure of a template/prototype database as a set-up for
severaldozen Fedora boxes. The dump used to work alright until very recently. Now, across new machines that are to be
introducedinto the network it consistently refuses to dump constraints and triggers - seems pg_dump just skips over
them.Otherwise the dump seems to be complete, the schema-table-column layout is complete. We thought it was the
templateserver problem, but the oddity is that a dump made with Windows version of pgAdmin3 comes out complete. 
> >
> > The command we use is:
> > /usr/bin/pg_dump -h <SERVER_IP> -p <SERVER_PORT> -U <SERVER_USER> -F p -N '*somename*' -N 'somename2' -N
'somename3'-N 'somename4' -N 'somename5' -T '*somename6*' -s -v -f /some/dir/schemacopy.sql <DATABASE_NAME> 
> >
> > The dump is made using a script we consider to be stable and therefore it hasn't changed since a long while ago. We
alsoweren't able to pin down any other change between the systems where it previously worked and the ones where it now
refusesto - the operating system (Fedora 16) is the same, the hardware is the same, the template database server is the
same.It doesn't matter whether we are running the script on an up-to-date system or an outdated-off-liveCD-installation
version,so it most probably is not update-related. The server (as a system) is sometimes under pretty much load so it
mightbe resource-related - be it currently or previously. 
> >
> > Searching through the archives, I have only stumbled upon a post from 2003 about a similar issue
(http://archives.postgresql.org/pgsql-admin/2003-08/msg00239.php)which might be connected, however, since the reporter
gaveup quickly, the issue remained unsolved. 
> >
> > How can we dig into this further? What might be happening?
>
> Postgres version?
> Is there more than one version of PG on machine?
> The dump made with PgAdmin uses the same parameters?
> Any errors in the logs on either the dump or restore side?
>
> >
> > Best regards,
> > Marek Kielar
> >
> >
>
>
>