Thread: pgdump
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.
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!
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.
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. >
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.
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!
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
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.
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
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/
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
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
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
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
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