pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump - txt sql vs binary
10+ messages / 4 participants
[nested] [flat]
* pg_dump - txt sql vs binary
@ 2006-01-22 20:41 Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Ciprian Hodorogea @ 2006-01-22 20:41 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-22 23:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2006-01-22 23:51 UTC (permalink / raw)
To: ciprian.hodorogea@bitsp.com; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-01-23 14:35 ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 15:56 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 15:57 ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 10+ messages in thread
From: Ciprian Hodorogea @ 2006-01-23 14:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
>> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-23 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2006-01-23 15:56 UTC (permalink / raw)
To: ciprian.hodorogea@bitsp.com; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-23 15:57 ` Michael Fuhr <mike@fuhr.org>
2006-01-23 16:49 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
1 sibling, 1 reply; 10+ messages in thread
From: Michael Fuhr @ 2006-01-23 15:57 UTC (permalink / raw)
To: Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 15:57 ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
@ 2006-01-23 16:49 ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 17:23 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Ciprian Hodorogea @ 2006-01-23 16:49 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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?
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 15:57 ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
2006-01-23 16:49 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-23 17:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-24 08:33 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2006-01-23 17:23 UTC (permalink / raw)
To: ciprian.hodorogea@bitsp.com; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 15:57 ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
2006-01-23 16:49 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 17:23 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-01-24 08:33 ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-24 21:24 ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 10+ messages in thread
From: Ciprian Hodorogea @ 2006-01-24 08:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin
>
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 15:57 ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
2006-01-23 16:49 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 17:23 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-24 08:33 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
@ 2006-01-24 21:24 ` Michael Fuhr <mike@fuhr.org>
2006-01-24 21:43 ` Re: pg_dump - txt sql vs binary Jaime Casanova <systemguards@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Fuhr @ 2006-01-24 21:24 UTC (permalink / raw)
To: Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_dump - txt sql vs binary
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 15:57 ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
2006-01-23 16:49 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 17:23 ` Re: pg_dump - txt sql vs binary Tom Lane <tgl@sss.pgh.pa.us>
2006-01-24 08:33 ` Re: pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-24 21:24 ` Re: pg_dump - txt sql vs binary Michael Fuhr <mike@fuhr.org>
@ 2006-01-24 21:43 ` Jaime Casanova <systemguards@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jaime Casanova @ 2006-01-24 21:43 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
> 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 ;)
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-01-24 21:43 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-22 20:41 pg_dump - txt sql vs binary Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-22 23:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 14:35 ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-23 15:57 ` Michael Fuhr <mike@fuhr.org>
2006-01-23 16:49 ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-23 17:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-24 08:33 ` Ciprian Hodorogea <ciprian.hodorogea@bitsp.com>
2006-01-24 21:24 ` Michael Fuhr <mike@fuhr.org>
2006-01-24 21:43 ` Jaime Casanova <systemguards@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox