PostgreSQL Administration - pg_dump

Author

pg_dump - txt sql vs binary

Ciprian Hodorogea

2006-01-22, 8:23 pm

Hi All,

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.

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?

All I want is to obtain a valid SQL file that I can then import within any
upcoming version of Postgres. Does anyone have any suggestion please?

Best regards,

Ciprian Hodorogea

IntelliSoft SRL

http://www.bitsp.com/

Tom Lane

2006-01-22, 8:23 pm

"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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Ciprian Hodorogea

2006-01-23, 9:23 am

>> 2. All dumps generated with pg_dump seem to contain internal functions[color=dark
red]

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-01-23, 11:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Michael Fuhr

2006-01-23, 11:24 am

On Mon, Jan 23, 2006 at 04:35:20PM +0200, Ciprian Hodorogea wrote:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Ciprian Hodorogea

2006-01-23, 11:24 am

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_handle
r'
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:
>
>
>
> 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?
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2006-01-23, 1:23 pm

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_handle
r'
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Ciprian Hodorogea

2006-01-24, 3:23 am

>
> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Michael Fuhr

2006-01-24, 8:24 pm

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...

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jaime Casanova

2006-01-24, 8:24 pm

> On Tue, Jan 24, 2006 at 10:33:50AM +0200, Ciprian Hodorogea wrote:
>
>
> 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 ;)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links