Thread: on line numbers, drop table errors, and log files
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 LaphamCentro Nacional de Ressonancia MagneticaNuclear de MacromoleculasUniversidade Federal do Rio de Janeiro (UFRJ) - Brasilemail: 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 mostquotes 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 usepassword 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
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 LaphamCentro Nacional de Ressonancia MagneticaNuclear de MacromoleculasUniversidade Federal do Rio de Janeiro (UFRJ) - Brasilemail: jlapham@gandalf.bioqmed.ufrj.br ***-*--*----*-------*------------*--------------------*---------------
> 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
"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