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, 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(dot)Elphick(at)lfix(dot)co(dot)uk>
Version: 7.0.2-4

Kind regards

Andreas.

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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

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(at)Fam-Meskes(dot)De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

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(at)gmx(dot)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(at)home(dot)com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

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(at)cat-it(dot)co(dot)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