Thread: pg_dump - txt sql vs binary

"Ciprian Hodorogea" <ciprian.hodorogea@bitsp.com> writes:
>   1. I noticed that binary dumps created with Postgres 8.0.3 can not be
> imported with Postgres 8.1.x, so this is not a good backup idea.

If you mean a filesystem dump, no.

>   2. All dumps generated with pg_dump seem to contain internal functions
> also, which then raise problems when trying to import. Can these be somehow
> excluded from the generated SQL backup?

Details please?

            regards, tom lane

>>  2. All dumps generated with pg_dump seem to contain internal functions
>>also, which then raise problems when trying to import. Can these be somehow
>>excluded from the generated SQL backup?

Is there a way to differentiate postgres internal functions from
user-defined functions? (other but making up a list of those functions
names)

Regards, Ciprian

Ciprian Hodorogea <ciprian.hodorogea@bitsp.com> writes:
> 2. All dumps generated with pg_dump seem to contain internal functions
> also, which then raise problems when trying to import. Can these be somehow
> excluded from the generated SQL backup?

> Is there a way to differentiate postgres internal functions from
> user-defined functions? (other but making up a list of those functions
> names)

I can think of several possibilities, but it's not clear what you need
to accomplish.  Would you show us details of the problem you're having,
rather than jumping to conclusions about how to fix it?  Bear in mind
that the rest of the world has found no need to do any such thing while
using pg_dump.

            regards, tom lane

On Mon, Jan 23, 2006 at 04:35:20PM +0200, Ciprian Hodorogea wrote:
> >> 2. All dumps generated with pg_dump seem to contain internal functions
> >>also, which then raise problems when trying to import. Can these be
> >>somehow excluded from the generated SQL backup?
>
> Is there a way to differentiate postgres internal functions from
> user-defined functions? (other but making up a list of those functions
> names)

What "internal" functions are you talking about?  What's the exact
command you're running and what's the exact error message or
unexpected behavior you're seeing?

--
Michael Fuhr

Command I have used:
pg_dump.exe -i -h localhost -p 5432 -U postgres -F p -v -f "C:\test.sql"
bicms_server

This will put in the output SQL file postgres functions also (not only
those defined by me), for example:

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

CREATE TYPE tablefunc_crosstab_2 AS (
    row_name text,
    category_1 text,
    category_2 text
);

CREATE FUNCTION connectby(text, text, text, text, integer, text) RETURNS
SETOF record
     AS '$libdir/tablefunc', 'connectby_text'
     LANGUAGE c STABLE STRICT;


ALTER FUNCTION public.connectby(text, text, text, text, integer, text)
OWNER TO postgres;


...and many others.



Anyway, my question would be: if I have postgres 8.1.x installed on
computer A and the same version of postgres installed on computer B,
which would be your recommended way of backing up data on computer A and
put it on computer B?


Regards, Ciprian

Michael Fuhr wrote:
> On Mon, Jan 23, 2006 at 04:35:20PM +0200, Ciprian Hodorogea wrote:
>
>>>>2. All dumps generated with pg_dump seem to contain internal functions
>>>>also, which then raise problems when trying to import. Can these be
>>>>somehow excluded from the generated SQL backup?
>>
>>Is there a way to differentiate postgres internal functions from
>>user-defined functions? (other but making up a list of those functions
>>names)
>
>
> What "internal" functions are you talking about?  What's the exact
> command you're running and what's the exact error message or
> unexpected behavior you're seeing?
>

Ciprian Hodorogea <ciprian.hodorogea@bitsp.com> writes:
> This will put in the output SQL file postgres functions also (not only
> those defined by me), for example:

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

> CREATE TYPE tablefunc_crosstab_2 AS (
>     row_name text,
>     category_1 text,
>     category_2 text
> );

> CREATE FUNCTION connectby(text, text, text, text, integer, text) RETURNS
> SETOF record
>      AS '$libdir/tablefunc', 'connectby_text'
>      LANGUAGE c STABLE STRICT;


Those *were* all defined by you, at some point, because they aren't
preloaded in the system.  Perhaps you have forgotten running the
contrib-module installation scripts, but you evidently did.

If you are having difficulty in reloading the definitions it suggests
that your new installation is lacking the contrib modules.  You could
install them, or manually remove the unwanted stuff from the dump file,
or drop the functions from the source installation before making a dump.
(Some but not all of the contrib modules have "uninstall" scripts, which
would help with the last alternative.)

> Anyway, my question would be: if I have postgres 8.1.x installed on
> computer A and the same version of postgres installed on computer B,
> which would be your recommended way of backing up data on computer A and
> put it on computer B?

pg_dump is certainly the preferred method.

            regards, tom lane

>
> pg_dump is certainly the preferred method.
>

I have Postgres 8.1.1 installed on a Windows machine and on a Linux
machine. When I do pg_dump (from Windows) and then pg_restore from
Linux, I get 52 errors, which I ignore and things seem to work fine, but
I suppose something is wrong about this...

It is only the setup from the website that I have installed and not
other contribs.

Best Regards, Ciprian

On Tue, Jan 24, 2006 at 10:33:50AM +0200, Ciprian Hodorogea wrote:
> >pg_dump is certainly the preferred method.
>
> I have Postgres 8.1.1 installed on a Windows machine and on a Linux
> machine. When I do pg_dump (from Windows) and then pg_restore from
> Linux, I get 52 errors, which I ignore and things seem to work fine, but
> I suppose something is wrong about this...

As Tom mentioned, the Windows box apparently has some contributed
modules that the Linux box doesn't, so when you try to restore the
catalog entries on Linux you get errors.  If you know you don't
need those modules then you can ignore the errors; if you'd like
to have an error-free restore then either add the modules to Linux,
remove them from Windows, or delete them from the backup.  If you're
using pg_restore then you could use the -l/--list and -L/--use-list
options to omit certain objects from the restore.  See the documentation
for an example.

> It is only the setup from the website that I have installed and not
> other contribs.

Did you build from source or did you install a pre-built package?
If from source then the contributed modules are in the contrib
directory; if from a package then look around for another package
that contains the contributed modules.

--
Michael Fuhr

> On Tue, Jan 24, 2006 at 10:33:50AM +0200, Ciprian Hodorogea wrote:
> >
> > I have Postgres 8.1.1 installed on a Windows machine and on a Linux
> > machine. When I do pg_dump (from Windows) and then pg_restore from
> > Linux, I get 52 errors, which I ignore and things seem to work fine, but
> > I suppose something is wrong about this...
>
> > It is only the setup from the website that I have installed and not
> > other contribs.
>
> Did you build from source or did you install a pre-built package?
> If from source then the contributed modules are in the contrib
> directory; if from a package then look around for another package
> that contains the contributed modules.
>

when installing from the pg_installer
(http://pginstaller.projects.postgresql.org) it let you to install
contrib modules just selecting them (and easy way to do it... so they
appear to newbies as just other options in the server, and not as
contrib modules), also it defaults to install plpgsql in template1 and
let you install and activate (i don't know what it refers with
'activate') postgis and pl/java when selecting options for the
server...

it also install some pgadmin support functions by default...


just ignore warnings works for me because i don't use most of these
things, and for plpgsql i create it by hand in linux before run the
script generated by pg_dump

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)