Обсуждение: 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