Thread: pgdump

pgdump

From
Andreas Tille
Date:
Hello,

if I do a database dump via pg_dump also PostgreSQL internal tables
named pga_* are stored in the dump.  However if I drop a database and
create it via "create database <name>" those tables are created
automatically.  Restoring the old content of the database using
  cat <name>.dump | psql <name>
leads to warning messages caused by the existence of the pga_*
tables.  This is really annoing.  I checked all pg_dump options
if this could be avoided, but didn't found any.

I wonder if I should write a little script to remove the pga_*
stuff from the dump to get a warning-free restore.

Is there any usual solution for this problem?

Kind regards

         Andreas.


Re: pgdump

From
Michael Meskes
Date:
On Tue, Sep 19, 2000 at 09:15:32AM +0200, Andreas Tille wrote:
> if I do a database dump via pg_dump also PostgreSQL internal tables
> named pga_* are stored in the dump.  However if I drop a database and

pga_* are not really internal tables. The internal tables are named pg_*.
pga_* are tables created by pgaccess.

> create it via "create database <name>" those tables are created
> automatically.  Restoring the old content of the database using

What version are you using? On my 7.0.2 (actual Debian package, so I guess
you are using the same) they are definitely not created autiomatically.

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Re: pgdump

From
Andreas Tille
Date:
On Tue, 19 Sep 2000, Michael Meskes wrote:

> pga_* are not really internal tables. The internal tables are named pg_*.
> pga_* are tables created by pgaccess.
This is what I thought, but the following script:

#!/bin/sh
echo "create database test ;" | psql
pg_dump -n -c test

creates the output:

\connect - postgres
DROP TABLE pga_queries;
CREATE TABLE pga_queries (
    queryname character varying(64),
    querytype character,
    querycommand text,
    querytables text,
    querylinks text,
    queryresults text,
    querycomments text
);
REVOKE ALL on pga_queries from PUBLIC;
GRANT ALL on pga_queries to PUBLIC;
DROP TABLE pga_forms;
CREATE TABLE pga_forms (
    formname character varying(64),
    formsource text
);
REVOKE ALL on pga_forms from PUBLIC;
GRANT ALL on pga_forms to PUBLIC;
DROP TABLE pga_scripts;
CREATE TABLE pga_scripts (
    scriptname character varying(64),
    scriptsource text
);
REVOKE ALL on pga_scripts from PUBLIC;
GRANT ALL on pga_scripts to PUBLIC;
DROP TABLE pga_reports;
CREATE TABLE pga_reports (
    reportname character varying(64),
    reportsource text,
    reportbody text,
    reportprocs text,
    reportoptions text
);
REVOKE ALL on pga_reports from PUBLIC;
GRANT ALL on pga_reports to PUBLIC;
DROP TABLE pga_schema;
CREATE TABLE pga_schema (
    schemaname character varying(64),
    schematables text,
    schemalinks text
);
REVOKE ALL on pga_schema from PUBLIC;
GRANT ALL on pga_schema to PUBLIC;
DROP FUNCTION plpgsql_call_handler ();
CREATE FUNCTION plpgsql_call_handler ( ) RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C';
DROP PROCEDURAL LANGUAGE 'plpgsql';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';
COPY pga_queries FROM stdin;
\.
COPY pga_forms FROM stdin;
\.
COPY pga_scripts FROM stdin;
\.
COPY pga_reports FROM stdin;
\.
COPY pga_schema FROM stdin;
\.

That's really annoying.

> > create it via "create database <name>" those tables are created
> > automatically.  Restoring the old content of the database using
>
> What version are you using? On my 7.0.2 (actual Debian package, so I guess
> you are using the same) they are definitely not created autiomatically.
Hmm, yes I use also the Debian-Packages from woody.

~> dpkg --status postgresql
Package: postgresql
Status: install ok installed
Priority: optional
Section: misc
Installed-Size: 1932
Maintainer: Oliver Elphick <Oliver.Elphick@lfix.co.uk>
Version: 7.0.2-4

Kind regards

        Andreas.


RE: Re: pgdump

From
"Trewern, Ben"
Date:
Have you used pgaccess on template1 database.  I assume that it creates
these tables in any db it sees.  After that 'create database' will make
these tables as you describe.

Remove them from template1 should fix this.

Ben

> -----Original Message-----
> From: Andreas Tille [mailto:tillea@rki.de]
> Sent: 19 September 2000 09:18
> To: Michael Meskes
> Cc: PostgreSQL General
> Subject: [GENERAL] Re: pgdump
>
>
> On Tue, 19 Sep 2000, Michael Meskes wrote:
>
> > pga_* are not really internal tables. The internal tables
> are named pg_*.
> > pga_* are tables created by pgaccess.
> This is what I thought, but the following script:
>
> #!/bin/sh
> echo "create database test ;" | psql
> pg_dump -n -c test
>
> creates the output:
>
> \connect - postgres
> DROP TABLE pga_queries;
> CREATE TABLE pga_queries (
>     queryname character varying(64),
>     querytype character,
>     querycommand text,
>     querytables text,
>     querylinks text,
>     queryresults text,
>     querycomments text
> );
> REVOKE ALL on pga_queries from PUBLIC;
> GRANT ALL on pga_queries to PUBLIC;
> DROP TABLE pga_forms;
> CREATE TABLE pga_forms (
>     formname character varying(64),
>     formsource text
> );
> REVOKE ALL on pga_forms from PUBLIC;
> GRANT ALL on pga_forms to PUBLIC;
> DROP TABLE pga_scripts;
> CREATE TABLE pga_scripts (
>     scriptname character varying(64),
>     scriptsource text
> );
> REVOKE ALL on pga_scripts from PUBLIC;
> GRANT ALL on pga_scripts to PUBLIC;
> DROP TABLE pga_reports;
> CREATE TABLE pga_reports (
>     reportname character varying(64),
>     reportsource text,
>     reportbody text,
>     reportprocs text,
>     reportoptions text
> );
> REVOKE ALL on pga_reports from PUBLIC;
> GRANT ALL on pga_reports to PUBLIC;
> DROP TABLE pga_schema;
> CREATE TABLE pga_schema (
>     schemaname character varying(64),
>     schematables text,
>     schemalinks text
> );
> REVOKE ALL on pga_schema from PUBLIC;
> GRANT ALL on pga_schema to PUBLIC;
> DROP FUNCTION plpgsql_call_handler ();
> CREATE FUNCTION plpgsql_call_handler ( ) RETURNS opaque AS
> '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C';
> DROP PROCEDURAL LANGUAGE 'plpgsql';
> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
> plpgsql_call_handler LANCOMPILER 'PL/pgSQL';
> COPY pga_queries FROM stdin;
> \.
> COPY pga_forms FROM stdin;
> \.
> COPY pga_scripts FROM stdin;
> \.
> COPY pga_reports FROM stdin;
> \.
> COPY pga_schema FROM stdin;
> \.
>
> That's really annoying.
>
> > > create it via "create database <name>" those tables are created
> > > automatically.  Restoring the old content of the database using
> >
> > What version are you using? On my 7.0.2 (actual Debian
> package, so I guess
> > you are using the same) they are definitely not created
> autiomatically.
> Hmm, yes I use also the Debian-Packages from woody.
>
> ~> dpkg --status postgresql
> Package: postgresql
> Status: install ok installed
> Priority: optional
> Section: misc
> Installed-Size: 1932
> Maintainer: Oliver Elphick <Oliver.Elphick@lfix.co.uk>
> Version: 7.0.2-4
>
> Kind regards
>
>         Andreas.
>

RE: Re: pgdump

From
Andreas Tille
Date:
On Tue, 19 Sep 2000, Trewern, Ben wrote:

> Have you used pgaccess on template1 database.  I assume that it creates
> these tables in any db it sees.  After that 'create database' will make
> these tables as you describe.
>
> Remove them from template1 should fix this.
Thanks, this works.

Kind regards

        Andreas.


Re: Re: pgdump

From
Michael Meskes
Date:
On Tue, Sep 19, 2000 at 09:42:51AM +0100, Trewern, Ben wrote:
> Have you used pgaccess on template1 database.  I assume that it creates
> these tables in any db it sees.  After that 'create database' will make

Yes, it does.

> these tables as you describe.

I agree that should be the reason.

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Re: pgdump

From
"Ross J. Reedstrom"
Date:
On Tue, Sep 19, 2000 at 10:04:15AM -0700, Michael Meskes wrote:
> On Tue, Sep 19, 2000 at 09:15:32AM +0200, Andreas Tille wrote:
> > if I do a database dump via pg_dump also PostgreSQL internal tables
> > named pga_* are stored in the dump.  However if I drop a database and
>
> pga_* are not really internal tables. The internal tables are named pg_*.
> pga_* are tables created by pgaccess.
>
> > create it via "create database <name>" those tables are created
> > automatically.  Restoring the old content of the database using
>
> What version are you using? On my 7.0.2 (actual Debian package, so I guess
> you are using the same) they are definitely not created autiomatically.
>

I'd guess that Andreas must have looked at the template1 database with
pgaccess.  That would create the pga_* tables in there, which would then
propagate to all newly crreated dbs. Pgaccess makes these files behind
the users back.  It might be reasonable for it _not_ to create them in
template1, if possible, without prompting the user, at least. I'll ask
Constantin about it.

The workaround is to go into template1 with psql and drop the pga_*
tables, then never use pgaccess to look in there.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: pgdump

From
Andreas Tille
Date:
On Tue, 19 Sep 2000, Ross J. Reedstrom wrote:

> I'd guess that Andreas must have looked at the template1 database with
> pgaccess.
Yes, this was the reason.

> That would create the pga_* tables in there, which would then
> propagate to all newly crreated dbs. Pgaccess makes these files behind
> the users back.  It might be reasonable for it _not_ to create them in
> template1, if possible, without prompting the user, at least. I'll ask
> Constantin about it.
That's a nice idea!

This reminds me to a further issue on this topic:
By accident I filed a dump not to the intended database, but to
template1.  This is not hard to do because I wrote a script like

#!/bin/sh
MYDB=<some_function>
cat dumpfile | psql $MYDB

unfortunately I hadn't checked whether $MYDB could be "" :-(.

So I filled my template1 database with a lot of rubish.
Nice exercise to remove this rubish which introduced me a little bit
deeper into PostgreSQL internal tables :).  Hope that I got rid off
all this stuff.

So the idea is to make it a little bit harder to put something into
template1 or, alternatively serve a method which helps out such kind
of situation.

.... just an idea ...

> The workaround is to go into template1 with psql and drop the pga_*
> tables, then never use pgaccess to look in there.
Or just to call my script which removes all tables and sequences
which are not created by user postgres :).

Kind regards

         Andreas.


import CVS file

From
Fernando Lozano
Date:
Hi there!

I need to import a CVS file generated by SQL Server into PostgreSQL
but I could not no ready utility to do this on postgresql.org. A
command line utility like msqlimport would be perfect, as I need to
use this on cron jobs. Anyone there can pelase pointe to such
utility?


[]s, Fernando Lozano



Re: import CVS file

From
Peter Eisentraut
Date:
Fernando Lozano writes:

> I need to import a CVS file generated by SQL Server into PostgreSQL

CSV?

> but I could not no ready utility to do this on postgresql.org. A
> command line utility like msqlimport would be perfect, as I need to
> use this on cron jobs. Anyone there can pelase pointe to such
> utility?

Use the COPY command.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Re: import CVS file

From
Fernando Lozano
Date:
Peter,


> > I need to import a CVS file generated by SQL Server into PostgreSQL
>
> CSV?

Right. :-)


> > but I could not no ready utility to do this on postgresql.org. A
> > command line utility like msqlimport would be perfect, as I need to
> > use this on cron jobs. Anyone there can pelase pointe to such
> > utility?
>
> Use the COPY command.


Someone said this command only accepts TAB delimited files, with no
string delimiter. Is it possible to change these settings?


[]s, Fernando Lozano



Re: import CVS file

From
Neil Conway
Date:
On Wed, Sep 20, 2000 at 10:43:17PM -0300, Fernando Lozano wrote:
> > Use the COPY command.
>
> Someone said this command only accepts TAB delimited files, with no
> string delimiter. Is it possible to change these settings?

Have a look at:

    http://www.postgresql.org/docs/user/sql-copy.htm

But, yes, you can change the delimiter using the 'DELIMITERS' clause.

HTH,

Neil

--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Attachment

Re: import CVS file

From
Andrew McMillan
Date:
Fernando Lozano wrote:
>
> Hi there!
>
> I need to import a CVS file generated by SQL Server into PostgreSQL
> but I could not no ready utility to do this on postgresql.org. A
> command line utility like msqlimport would be perfect, as I need to
> use this on cron jobs. Anyone there can pelase pointe to such
> utility?

I have some perl scripts which I use to do this - you are welcome to
them.

Regards,
                    Andrew.
--
_____________________________________________________________________
            Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

Re: pgdump

From
Adrian Klaver
Date:
On 01/30/2015 01:16 AM, Ramesh T wrote:
> when i try to dump the database windows based pgadmin 3 it returns
> message like
>
> pg_dump: server version: 9.3.4; pg_dump version: 9.1.3
> pg_dump: aborting because of server version mismatch
>
> any help..?

Use a 9.3+ version of pg_dump. A version of pg_dump is backwards
compatible so it can work on its version to Postgres versions going back
to 7.2, I believe. That does not work the other way around as you found
above. pgAdmin3 is picking up a 9.1.3 version of pg_dump and trying to
use it to dump a 9.3 version of Postgres.  So you will need to find the
9.3 version of pg_dump and use that.

>
> Advanced thanks,


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: pgdump

From
Raymond O'Donnell
Date:
On 30/01/2015 14:46, Adrian Klaver wrote:
> On 01/30/2015 01:16 AM, Ramesh T wrote:
>> when i try to dump the database windows based pgadmin 3 it returns
>> message like
>>
>> pg_dump: server version: 9.3.4; pg_dump version: 9.1.3
>> pg_dump: aborting because of server version mismatch
>>
>> any help..?
>
> Use a 9.3+ version of pg_dump. A version of pg_dump is backwards
> compatible so it can work on its version to Postgres versions going back
> to 7.2, I believe. That does not work the other way around as you found
> above. pgAdmin3 is picking up a 9.1.3 version of pg_dump and trying to
> use it to dump a 9.3 version of Postgres.  So you will need to find the
> 9.3 version of pg_dump and use that.

To add to what Adrian says, you tell pgAdmin which pg_dump (and other
Postgres client programs) to use under File -> Options -> Browser ->
Binary paths, in the "PG bin path" field (this is on PgAdmin III 1.20).

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie