pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
on line numbers, drop table errors, and log files
5+ messages / 3 participants
[nested] [flat]

* on line numbers, drop table errors, and log files
@ 2000-08-01 13:56 Jon Lapham <jlapham@gandalf.bioqmed.ufrj.br>
  0 siblings, 0 replies; 5+ messages in thread

From: Jon Lapham @ 2000-08-01 13:56 UTC (permalink / raw)
  To: pgsql-sql

Hello-

A few quick questions, I'm interested to know how people solve these
issues.  Thanks!

(BTW: Pg 7.0.2)

1) Is there a way to find discover the line number of a script that causes
an error?  I mean, if I "\i script.sql" a 2000 line script, it is often
difficult to track down that it was line #1211 that has a problem.  What I
have been doing recently (don't laugh) is to iteratively chop the script
up into smaller pieces until I find the offensive command.

2) Is there a way to write a script such that it only drops a table IF it
exists?  Currently, I just place a "drop table blah" in front of a "create
table blah" and live with the error message.  I've noticed that some
people make a "drop table" script and a "create table" script, is this the
generally best way to do things?  The problems arise in that sometimes I
do not know (because things are automated) if the table exists yet or not.

3) Is it possible to have psql make a log file of a session?  This would
solve problem #1 above, because one could look in the log file to find the
first error message from the large script.

4) I run pg_dump this way: "pg_dump -D -u -a -f backup.dump main_v0_6" in
order to create a backup of my "main_v0_6" database.  In order to
regenerate the database, I first run a "\i create_tables.sql" script, then
I run "\i backup.dump".  However, I end up having to hand edit the
"backup.dump" file to place "drop sequence" statements before the "create
sequence" statements because they are created by my
"create_tables.sql" script.  I'm sure you Postgres gurus out there are not
doing it this way, what is your method of database backup/recovery?

TIA, Jon

-- 

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
 Jon Lapham
 Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
 Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
 email: jlapham@gandalf.bioqmed.ufrj.br  
***-*--*----*-------*------------*--------------------*---------------



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: on line numbers, drop table errors, and log files
@ 2000-08-01 18:32 Michael Richards <michael@fastmail.ca>
  2000-08-02 13:02 ` Re: on line numbers, drop table errors, and log files Jon Lapham <jlapham@gandalf.bioqmed.ufrj.br>
  0 siblings, 1 reply; 5+ messages in thread

From: Michael Richards @ 2000-08-01 18:32 UTC (permalink / raw)
  To: pgsql-sql; +Cc: jlapham@gandalf.bioqmed.ufrj.br

Hi.

Here are the options for pg_dump:
  -a         dump out only the data, no schema
  -c         clean (drop) schema prior to create
  -d         dump data as INSERT, rather than COPY, commands
  -D         dump data as INSERT commands with attribute names
  -h <hostname>   server host name
  -i         proceed when database version != pg_dump version
  -n         suppress most quotes around identifiers
  -N         enable most quotes around identifiers
  -o         dump object ids (oids)
  -p <port>  server port number
  -s         dump out only the schema, no data
  -t <table> dump for this table only
  -u         use password authentication
  -v         verbose
  -x         do not dump ACL's (grant/revoke)

So you are dumping it with the following:
Proper INSERT commands/attribs, pwd auth, data only

I would consider running:
pg_dump -D -u -c -f backup.dump
This will dump the schema and drop the objects before creating them.

-Michael

> 4) I run pg_dump this way: "pg_dump -D -u -a -f backup.dump
> main_v0_6" in order to create a backup of my "main_v0_6" database.
>  In order to regenerate the database, I first run a "\i
> create_tables.sql" script, then I run "\i backup.dump".  However,
> I end up having to hand edit the "backup.dump" file to place "drop
> sequence" statements before the "create sequence" statements
> because they are created by my "create_tables.sql" script.  I'm
> sure you Postgres gurus out there are not doing it this way, what
> is your method of database backup/recovery?

_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: on line numbers, drop table errors, and log files
  2000-08-01 18:32 Re: on line numbers, drop table errors, and log files Michael Richards <michael@fastmail.ca>
@ 2000-08-02 13:02 ` Jon Lapham <jlapham@gandalf.bioqmed.ufrj.br>
  0 siblings, 0 replies; 5+ messages in thread

From: Jon Lapham @ 2000-08-02 13:02 UTC (permalink / raw)
  To: Michael Richards <michael@fastmail.ca>; +Cc: pgsql-sql

On Tue, Aug 01, 2000 at 02:32:27PM -0400, Michael Richards wrote:
> I would consider running:
> pg_dump -D -u -c -f backup.dump
> This will dump the schema and drop the objects before creating them.
> -Michael
> 
> > 4) I run pg_dump this way: "pg_dump -D -u -a -f backup.dump
> > main_v0_6" in order to create a backup of my "main_v0_6" database.
> >  In order to regenerate the database, I first run a "\i
> > create_tables.sql" script, then I run "\i backup.dump".  However,
> > I end up having to hand edit the "backup.dump" file to place "drop
> > sequence" statements before the "create sequence" statements
> > because they are created by my "create_tables.sql" script.  I'm
> > sure you Postgres gurus out there are not doing it this way, what
> > is your method of database backup/recovery?

Michael-

	As I mentioned earlier, your solution worked great.  I am a bit
puzzled about the syntax that is created by my old method, using "pg_dump
-D -u -a".  I wonder why it creates "create sequence ..." commands instead
of "update sequence ..."?

	The reason I was originally using "-a" to begin with was that I
wanted to seperate the data entry from the table creation step.  So, the
idea was that I would have a nice hand editted script to make all my
tables, then the "-a" output of pg_dump would fill the database with data.

	As I said, your solution works, thanks, I'm just puzzled why my
way didn't.

	Later, Jon

-- 

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
 Jon Lapham
 Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
 Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
 email: jlapham@gandalf.bioqmed.ufrj.br  
***-*--*----*-------*------------*--------------------*---------------



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: on line numbers, drop table errors, and log files
@ 2000-08-02 21:42 Michael Richards <michael@fastmail.ca>
  2000-08-02 21:54 ` Re: on line numbers, drop table errors, and log files Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Michael Richards @ 2000-08-02 21:42 UTC (permalink / raw)
  To: jlapham@gandalf.bioqmed.ufrj.br; +Cc: pgsql-sql


> As I mentioned earlier, your solution worked great.  I am a bit
> puzzled about the syntax that is created by my old method, using
> "pg_dump -D -u -a".  I wonder why it creates "create sequence ..."
> commands instead of "update sequence ..."?

That is a good question. I do not know the answer. Since it is 
understood that you are dumping the data and not the schema, that 
would imply to me that the schema (sequences included) should already 
exist and need to be updated. Of course I feel that implicitly it 
should be cleaning out the contents of the tables when this is done 
as well.

Perhaps someone on the team could comment on this.

-Michael
_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: on line numbers, drop table errors, and log files
  2000-08-02 21:42 Re: on line numbers, drop table errors, and log files Michael Richards <michael@fastmail.ca>
@ 2000-08-02 21:54 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2000-08-02 21:54 UTC (permalink / raw)
  To: Michael Richards <michael@fastmail.ca>; +Cc: jlapham@gandalf.bioqmed.ufrj.br; pgsql-sql

"Michael Richards" <michael@fastmail.ca> writes:
> Perhaps someone on the team could comment on this.

I doubt that whoever added pg_dump's sequence support thought very hard
about the "dump data only" case.  Feel free to propose a different
behavior, especially if you're prepared to implement it ;-)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-08-02 21:54 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-01 13:56 on line numbers, drop table errors, and log files Jon Lapham <jlapham@gandalf.bioqmed.ufrj.br>
2000-08-01 18:32 Re: on line numbers, drop table errors, and log files Michael Richards <michael@fastmail.ca>
2000-08-02 13:02 ` Jon Lapham <jlapham@gandalf.bioqmed.ufrj.br>
2000-08-02 21:42 Re: on line numbers, drop table errors, and log files Michael Richards <michael@fastmail.ca>
2000-08-02 21:54 ` Tom Lane <tgl@sss.pgh.pa.us>

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