Thread: Forcing pg_dump NOT to use "INSERT..."

Forcing pg_dump NOT to use "INSERT..."

From
"David F. Skoll"
Date:
Hi,

On Red Hat 9, when I run the pg_dump command, it uses the
"INSERT ...." dump format.  I thought it only did that if you used the
--inserts option.  How do I force it NOT to use that format?

Regards,

David.

Re: Forcing pg_dump NOT to use "INSERT..."

From
"Jaskier"
Date:
Hi,

Yes that's right

But You can avoid it by adding

'-s, --schema-only' it will copy only structure without data

Regards,

Andrzej Schulz
Galileo-Selidor


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of David F. Skoll
Sent: Tuesday, November 04, 2003 3:42 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Forcing pg_dump NOT to use "INSERT..."

Hi,

On Red Hat 9, when I run the pg_dump command, it uses the
"INSERT ...." dump format.  I thought it only did that if you used the
--inserts option.  How do I force it NOT to use that format?

Regards,

David.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match



Re: Forcing pg_dump NOT to use "INSERT..."

From
Tom Lane
Date:
"David F. Skoll" <dfs@roaringpenguin.com> writes:
> On Red Hat 9, when I run the pg_dump command, it uses the
> "INSERT ...." dump format.  I thought it only did that if you used the
> --inserts option.  How do I force it NOT to use that format?

This is extremely hard to believe.  I can see no way that pg_dump will
do that unless you explicitly ask for it (-d or -D switch, or one of the
long variants of same).

            regards, tom lane

Microsoft access verses postgresql

From
Geoffrey
Date:
I've got a client who is following my suggestion that they replace a set
of excel spreadsheets with a database solution.  They are looking at two
proposals, postgresql solution or an Access solution.  The requirements
will include vpn connectivity from one site to another.  It appears they
will be going with the Access solution.  I've got concerns regarding
this based on research I've done that seems to indicate that Access,
when used in a multi-user solution is easily corrupted.  Does anyone
have any knowledge/experience with such issues?

--
Until later, Geoffrey    esoteric@3times25.net

Building secure systems inspite of Microsoft


Re: Microsoft access verses postgresql

From
"David Green"
Date:
I have been working on an in-house software package for over 3 years now.
I started out using an Access database but once we put more users on it
the database began corrupting frequently. We replaced hardware and the
problem would go away for a bit but would soon return. We switched to
SQL Server (using the eval) but we decided to try switching to PostgreSQL
before the 120 eval was up. We're still working on the MSSQL -> PostgreSQL
switch but once we got off of Access everything went good - no more
corruptions :)

David Green
Sage Automation, Inc.


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Geoffrey
Sent: Tuesday, November 04, 2003 11:46 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Microsoft access verses postgresql


I've got a client who is following my suggestion that they replace a set
of excel spreadsheets with a database solution.  They are looking at two
proposals, postgresql solution or an Access solution.  The requirements
will include vpn connectivity from one site to another.  It appears they
will be going with the Access solution.  I've got concerns regarding
this based on research I've done that seems to indicate that Access,
when used in a multi-user solution is easily corrupted.  Does anyone
have any knowledge/experience with such issues?

--
Until later, Geoffrey    esoteric@3times25.net

Building secure systems inspite of Microsoft


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


Re: Microsoft access verses postgresql

From
William Yu
Date:
Geoffrey wrote:
> I've got a client who is following my suggestion that they replace a set
> of excel spreadsheets with a database solution.  They are looking at two
> proposals, postgresql solution or an Access solution.  The requirements
> will include vpn connectivity from one site to another.  It appears they
> will be going with the Access solution.  I've got concerns regarding
> this based on research I've done that seems to indicate that Access,
> when used in a multi-user solution is easily corrupted.  Does anyone
> have any knowledge/experience with such issues?
>

My experience: Access, like any desktop-based database, is subject to
data corruption in multi-user environments. Pretty easy to picture when
every machine keeps chunks of data in their local memory, has to write
back to the network and then somehow push the updates to the other
clients. You *can* avoid data corruption using very strict locks -- but
you can't use Access' built-in table/row locking as those functions also
suffer from the same multi-user synchronization problem. What you can do
is to write your own locking functions based on file handle access. Make
a temp dir on a network and everytime you want to update/insert, attempt
to create a file based on the table/record (e.g. CUSTOMERS-0 for
inserts, CUSTOMERS-1000 for specific records) -- a valid file handle
would then mean you have a lock. For the most part, you can avoid data
corruption if you bypass the built-in locking functions but you will
still have to periodically take down the system during production hours
for reindexing when even these measures fail.


Re: Microsoft access verses postgresql

From
Kaarel
Date:
> I've got a client who is following my suggestion that they replace a set
> of excel spreadsheets with a database solution.  They are looking at two
> proposals, postgresql solution or an Access solution.  The requirements
> will include vpn connectivity from one site to another.  It appears they
> will be going with the Access solution.  I've got concerns regarding
> this based on research I've done that seems to indicate that Access,
> when used in a multi-user solution is easily corrupted.  Does anyone
> have any knowledge/experience with such issues?

As far as I am concerned, Access is a bad joice if the data needs to be
accessed from more than one application at the same time. But if Excel
worked well enough and only one person is working with the data, then
Access might do just well.

Kaarel


Re: Microsoft access verses postgresql

From
Michelle Murrain
Date:
On Tue, 2003-11-04 at 12:46, Geoffrey wrote:
> I've got a client who is following my suggestion that they replace a set
> of excel spreadsheets with a database solution.  They are looking at two
> proposals, postgresql solution or an Access solution.  The requirements
> will include vpn connectivity from one site to another.  It appears they
> will be going with the Access solution.  I've got concerns regarding
> this based on research I've done that seems to indicate that Access,
> when used in a multi-user solution is easily corrupted.  Does anyone
> have any knowledge/experience with such issues?

Um, use both??

We've been having some success using Access front ends with postgresql
back ends. Takes away the data corruption/scalability issues, but keeps
the strength of the Access reporting and data entry interfaces.
--
.Michelle
---------------------------
Michelle Murrain
mmurrain@dbdes.com
Database Designs Associates, Inc.
Boston   617.889.0929
Amherst  413.253.2874
Cell      413.222.6350
www.dbdes.com

AIM:pearlbear0 ICQ:129250575

"Our capacity to make peace with another person and with the world
depends very much on our capacity to make peace with ourselves." -- Thich Nhat Hanh


Re: Microsoft access verses postgresql

From
Geoffrey
Date:
Michelle Murrain wrote:
> On Tue, 2003-11-04 at 12:46, Geoffrey wrote:
>
>>I've got a client who is following my suggestion that they replace a set
>>of excel spreadsheets with a database solution.  They are looking at two
>>proposals, postgresql solution or an Access solution.  The requirements
>>will include vpn connectivity from one site to another.  It appears they
>>will be going with the Access solution.  I've got concerns regarding
>>this based on research I've done that seems to indicate that Access,
>>when used in a multi-user solution is easily corrupted.  Does anyone
>>have any knowledge/experience with such issues?
>
>
> Um, use both??
>
> We've been having some success using Access front ends with postgresql
> back ends. Takes away the data corruption/scalability issues, but keeps
> the strength of the Access reporting and data entry interfaces.

Okay, so how do you approach this?  Access front end will talk to
postgresql?  Pointers to any docs would be appreciated.

--
Until later, Geoffrey    esoteric@3times25.net

Building secure systems inspite of Microsoft


Re: Microsoft access verses postgresql

From
Geoffrey
Date:
Kaarel wrote:
>> I've got a client who is following my suggestion that they replace a
>> set of excel spreadsheets with a database solution.  They are looking
>> at two proposals, postgresql solution or an Access solution.  The
>> requirements will include vpn connectivity from one site to another.
>> It appears they will be going with the Access solution.  I've got
>> concerns regarding this based on research I've done that seems to
>> indicate that Access, when used in a multi-user solution is easily
>> corrupted.  Does anyone have any knowledge/experience with such issues?
>
>
> As far as I am concerned, Access is a bad joice if the data needs to be
> accessed from more than one application at the same time. But if Excel
> worked well enough and only one person is working with the data, then
> Access might do just well.

Multiple people were attempting to access the excel spreadsheets.  Their
solution was for any person who modifies the spreadsheet was to copy it
to the other 4 computers.  Yes, that's correct.  I suggested a db
solution from the beginning, but they didn't want to spend the $$$, so I
suggested placing the excel spreadsheets on a single file server, thus
only one person could access a spreadsheet at one time and no more
copying files across their network.

--
Until later, Geoffrey    esoteric@3times25.net

Building secure systems inspite of Microsoft


Re: Microsoft access verses postgresql

From
Michelle Murrain
Date:
On Tue, 2003-11-04 at 21:39, Geoffrey wrote:

> Okay, so how do you approach this?  Access front end will talk to
> postgresql?  Pointers to any docs would be appreciated.

Access front end talks with Postgresql via ODBC. Can be a little flakey
at times, but it works well in my experience, although it does depend
some on the complexity you need.

There are a couple of different docs at: http://techdocs.postgresql.org/
- one good one on the step by step under "Technical Guides".

--
.Michelle
---------------------------
Michelle Murrain
mmurrain@dbdes.com
Database Designs Associates, Inc.
Boston   617.889.0929
Amherst  413.253.2874
Cell      413.222.6350
www.dbdes.com

AIM:pearlbear0 ICQ:129250575

"Our capacity to make peace with another person and with the world
depends very much on our capacity to make peace with ourselves." -- Thich Nhat Hanh


Re: Microsoft access verses postgresql

From
Geoffrey
Date:
Michelle Murrain wrote:
> On Tue, 2003-11-04 at 21:39, Geoffrey wrote:
>
>
>>Okay, so how do you approach this?  Access front end will talk to
>>postgresql?  Pointers to any docs would be appreciated.
>
>
> Access front end talks with Postgresql via ODBC. Can be a little flakey
> at times, but it works well in my experience, although it does depend
> some on the complexity you need.
>
> There are a couple of different docs at: http://techdocs.postgresql.org/
> - one good one on the step by step under "Technical Guides".

Thanks for the pointer, I'll check it out.


--
Until later, Geoffrey    esoteric@3times25.net

Building secure systems inspite of Microsoft


Re: Forcing pg_dump NOT to use "INSERT..."

From
Stef
Date:
=> This is extremely hard to believe.  I can see no way that pg_dump will
=> do that unless you explicitly ask for it (-d or -D switch, or one of the
=> long variants of same).

I know of a lot of people that confuses the -d switch as an option
indicating the database name to follow. It is also usually very hard
to convince people that they dumped INSERT statements in stead
of COPY once you sit with a restore that takes a little longer than
usual...


Attachment

Re: Microsoft access verses postgresql

From
Christopher Browne
Date:
A long time ago, in a galaxy far, far away, esoteric@3times25.net (Geoffrey) wrote:
> I've got a client who is following my suggestion that they replace a
> set of excel spreadsheets with a database solution.  They are
> looking at two proposals, postgresql solution or an Access solution.
> The requirements will include vpn connectivity from one site to
> another.  It appears they will be going with the Access solution.
> I've got concerns regarding this based on research I've done that
> seems to indicate that Access, when used in a multi-user solution is
> easily corrupted.  Does anyone have any knowledge/experience with
> such issues?

If the application will be lightly loaded, it may not be a big deal.
(After all, if the load is light enough, people might be able to do
the job by filling in 3"x5" index cards and filing them in a box.)

But if there are a bunch of users _actively_ updating the database,
Access gets unacceptable pretty quickly.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www.ntlug.org/~cbbrowne/rdbms.html
"Linux poses  a real challenge for  those with a  taste for late-night
hacking (and/or conversations with God)." -- Matt Welsh

Re: Microsoft access verses postgresql

From
Christopher Browne
Date:
Centuries ago, Nostradamus foresaw when esoteric@3times25.net (Geoffrey) would write:
> Michelle Murrain wrote:
>> On Tue, 2003-11-04 at 12:46, Geoffrey wrote:
>>
>>> I've got a client who is following my suggestion that they replace
>>> a set of excel spreadsheets with a database solution.  They are
>>> looking at two proposals, postgresql solution or an Access
>>> solution.  The requirements will include vpn connectivity from one
>>> site to another.  It appears they will be going with the Access
>>> solution.  I've got concerns regarding this based on research I've
>>> done that seems to indicate that Access, when used in a multi-user
>>> solution is easily corrupted.  Does anyone have any
>>> knowledge/experience with such issues?
>> Um, use both??
>> We've been having some success using Access front ends with
>> postgresql
>> back ends. Takes away the data corruption/scalability issues, but keeps
>> the strength of the Access reporting and data entry interfaces.
>
> Okay, so how do you approach this?  Access front end will talk to
> postgresql?  Pointers to any docs would be appreciated.

Use the ODBC driver, just as you would if connecting Access to any
other Real Database System.

<http://gborg.postgresql.org/project/psqlodbc/projdisplay.php>

You don't get perfection for free; MS Access hasn't got a perfect
model for dealing with concurrent access to data, but at least file
corruption should go away, and you'll be able to initiate backups
without having to kick all the users out.

There will still be something of a scalability issue, at least
compared to SQL Server; the latter has a scheme where queries
automagically get turned into cursor declarations.  But you should be
a good few steps ahead...
--
(format nil "~S@~S" "aa454" "freenet.carleton.ca")
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"I think that helps the users too much."   -- CSTACY