Thread: 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.
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
"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
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
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
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.
> 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
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
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
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
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
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
=> 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
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
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