Thread: Adaptation of instnwnd.sql to Pgsql
Listers... I am new to PostGreSQL and I am trying to adapt the instnwnd.sql stored procedure from SQL Server to create the ubiquitous NorthWind database structure on my server. However the ability to name your indexes seems to be non existent. As is the ability to create indexes of the same name on different tables. Does anyone out there have an adapted Pgsql version of this script ? It would be invaluable to me to get quickly up to speed on this version of the SQL standard by comparing what I know from TSQL to what the equivalent command syntax is in pgsql. regards David David A. Jacques Spatial Database Developer Natural Resources and Hazards Section Applications Division Canada Centre for Remote Sensing 588 Booth Street, Room 317 Ottawa ,Ontario K1A 0Y7 Tel: (613) 947-1306 Fax: (613) 947-1408 E-Mail: David.Jacques@CCRS.NRCan.gc.ca The views and ideas expressed in this communication are hereby deemed to be my own and in no way can be construed as endorsed by the CCRS or the Government of Canada.
David.Jacques@CCRS.NRCan.gc.ca wrote: > Listers... > > I am new to PostGreSQL and I am trying to adapt the > instnwnd.sql stored procedure from SQL Server to create the > ubiquitous NorthWind database structure on my server. However > the ability to name your indexes seems to be non existent. This is not true. During the index creation you can specify the name, take a look at the documentation. > As is the ability to create indexes of the same name on different tables. About this you're right, index name are DB wide... Regards Gaetano Mendola
Gaetano Mendola <mendola@bigfoot.com> writes:
> David.Jacques@CCRS.NRCan.gc.ca wrote:
>> As is the ability to create indexes of the same name on different tables.
> About this you're right, index name are DB wide...
Only schema-wide, but that probably doesn't help David any...
regards, tom lane
Hello list, I am having problems getting pg_dump and pg_restore to work the way they are supposed to work - or at least the way I imagine they should work! I have a primary DB server, and a backup DB server. The point is to use pg_dump every 5 minutes to have the current postgeSQL db available on the backup DB box. btw, these are both running version 7.4.2. Here's the command I am issuing on the primary DB server: pg_dump -U admin staging -Fc -b -f /home/postgres/staging-dump And, it seems to work - creating a 20MB archive that has some readable text in it near the top, and a bunch of random characters to the end. According to the docs, this file should be able to used in many different ways by the pg_restore command. So, on the backup DB server, I am trying to completely rebuild the staging database to the exact state of the primary server with: pg_restore -U admin -c /home/postgres/staging-dump Now, although tons of data is now streaming to my monitor, when it is done, none of the new data is in the backup's staging db. So, I tried deleting "staging" on the backup server with: dropdb -U admin staging and then create it again: createdb -U dba staging -O admin And when I run the above pg_restore command, none of the data or schema is in the newly created staging db. So, I tried doing this variation: pg_restore -U admin -C -d staging /home/postgres/staging-dump but I get this error: pg_restore: [archiver (db)] could not execute query: ERROR: database "staging" already exists and if i don't recreate the staging db with the createdb command, I get this error: pg_restore: [archiver (db)] connection to database "staging" failed: FATAL: database "staging" does not exist I'm sure that I'm close to getting this to work - any advise? Thanks, Devin
admin@usachurch.com writes:
> pg_restore -U admin -c /home/postgres/staging-dump
> Now, although tons of data is now streaming to my monitor, when it is
> done, none of the new data is in the backup's staging db.
The default behavior of pg_restore is to emit SQL commands onto stdout,
which of course is not going to do a lot for your backup DB. What you
probably want is to include a "-d database" switch to tell it to connect
to a database and send the commands there.
> So, I tried doing this variation:
> pg_restore -U admin -C -d staging /home/postgres/staging-dump
> pg_restore: [archiver (db)] could not execute query: ERROR: database
> "staging" already exists
-C tells it to create (and then connect to) the target DB, rather than
connect to an existing DB and restore into that. So generally you want
to say "-d template1" (or some other existing DB) when you use -C.
regards, tom lane
> > pg_restore -U admin -C -d staging /home/postgres/staging-dump >> pg_restore: [archiver (db)] could not execute query: ERROR: database >> "staging" already exists > >-C tells it to create (and then connect to) the target DB, rather than >connect to an existing DB and restore into that. So generally you want >to say "-d template1" (or some other existing DB) when you use -C. Tom, But if I first "dropdb -U admin staging", and then issue: pg_restore -U admin -C -d staging /home/postgres/staging-dump I get the error: pg_restore: [archiver (db)] connection to database "staging" failed: FATAL: database "staging" does not exist Yet the docs state that the above command should Create the database named by the "-d staging" part of the command. I'm confused as to why that doesn't work. I also am not sure what the template1 and template0 are there for. Regards, Devin