Adaptation of instnwnd.sql to Pgsql

[D***@CCRS.NRCan.gc.ca]

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: [phone]
Fax: [phone]
E-Mail: ***@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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Gaetano Mendola]

Post by D***@CCRS.NRCan.gc.ca
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.

Post by D***@CCRS.NRCan.gc.ca
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

[Tom Lane]

Post by Gaetano Mendola

Post by D***@CCRS.NRCan.gc.ca
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[a***@usachurch.com]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Post by a***@usachurch.com
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.

Post by a***@usachurch.com
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[a***@usachurch.com]

Post by Tom Lane

Post by a***@usachurch.com
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend