[GENERAL] Explicit Named Indexes for Constraints
I'm new to PosgtreSQL, a veteran with Informix. We are considering a migration. In Informix, it is recommended to create explicit named indexes on columns for primary and foreign keys prior to creating the constraints. Otherwise, the server create the indexes for you with meaningless names. This is not generally a problem, except when you dump the schema, you get all the constraint indexes in the DDL, exported as if they were explicitly created, but with the server generated names. It's a mess to sort through. What's the recommended procedure in PG? At first glance it appears that PG hides the implicit indexes from you at all times, including pg_dump. So it appears that explicit index creation can be skipped without leaving you with a mess later. Is this just a non-issue in PG? Jeff ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Rollback capacity
Once again, I'm trying to translate my knowledge of Informix to PostgreSQL. I tried the manual and Google, but could not find anything relevant. Informix keeps transaction logs in a dedicated, pre-allocated disk area that, until very recent versions, could not grow dynamically. It is the DBA's responsibility to continually backup these transaction logs so the space may be recycled. As such, Informix is limited in the size of a transaction that it can roll back, because it eventually has to re-use existing transaction log space. If it were to overwrite the log space containing the beginning of the transaction, it could not rollback from the internal logs. So if you do something crazy, like delete 4 million rows, there's a good chance Informix will just throw an error "long transaction aborted" and roll it back when the transaction reaches a pre-set high water mark. How does PostgreSQL handle big transactions and potential rollbacks. Since the WAL is not strictly pre-allocated space, can it just keep going until the WAL files fill up the free disk space? What would be the consequences of such an incident (filling up disk space with WAL files)? Is the WAL even relevant to rollbacks? I am aware of the statement_timeout parameter which could prevent huge transactions, but there is no useful correlation between the time a statement takes and the server's capacity to roll it back. Thanks, Jeff ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] any way to query for current connections to db?
> Is there any way to query the database to identify what the current > connections are (connections, sessions, or whatever you want to call them)? select * from pg_stat_activity; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] any way to query for current connections to db?
> > Is this an FAQ? > > Yes, it gets asked a lot. > > Is it in the FAQ? Don't know. In the FAQ? No In the Manual? Yes ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgres schema printer
On Nov 25, 2007 3:32 PM, Dave Potts <[EMAIL PROTECTED]> wrote: > Is there such a thing as an opensource schema printer for postgres ? http://schemaspy.sourceforge.net/ Creates nice html pages and graphics for many database flavors. Uses Java/JDBC. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] replication in Postgres
> Someone is working on extending the current system to allow read-only > queries on a standby server [1], thus making it a "hot standby", but > this feature apparently won't be included until 8.4 [2]. My 2 cents... I would rather see someone working on true synchronous replication, rather than a readable hot-standby. Yeah, I know, different problems with different solutions. But,. for my money, a readable hot-standby doesn't give me added functionality, it's just a performance option. Whereas synchronous replication has much greater value from a the perspective of running a 24x7 business. I'm on the verge of dumping our commercial DB in favor of PostgreSQL, but this is one issue that is holding me back. Jeff ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] replication in Postgres
> Alvaro Herrera wrote: > > Glyn Astill wrote: > >> Thanks everyone for your replies. EnterpriseDB looks like the way to > >> go if we want good replication. > > > > Sorry, this makes no sense to me -- EnterpriseDB has no replication > > solution that I know of. > > Yeah, there is: > > http://www.enterprisedb.com/products/enterprisedb_replication.do Yes, but I'd like something better than "near real time" as the above page describes. Or maybe someone could clarify that Besides, EnterpriseDB does not save me enough money. In my current commercial DB, if a transaction is committed on the master, it is guaranteed to be committed to the secondary. In our business, losing one customer order could lose us the customer for good. Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication in Postgres
> > Yes, but I'd like something better than "near real time" as the above > > page describes. Or maybe someone could clarify that Besides, > > EnterpriseDB does not save me enough money. In my current commercial > > DB, if a transaction is committed on the master, it is guaranteed to > > be committed to the secondary. In our business, losing one customer > > order could lose us the customer for good. > > So you want synchronous replication. Search on that term in the > archives for possible solutions (or lack thereof) in postgres. > > If you don't specify your requirements clearly, don't expect useful > advice ;-) I'm not looking for advice. My original contribution to this thread suggested a preferred course of future development. I know what my options are with the present version, but I see room for improvement. Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgresSQL vs. Informix
On Nov 28, 2007 9:32 AM, Chad Hendren <[EMAIL PROTECTED]> wrote: > I have a large OEM customer (one of the top 8 for Sun worldwide) that is > considering embedding PostgreSQL into the core of their primary product > instead of Informix. He is trying to build his case for this change. > Do we have anything that I can forward to him (something like the > presentation you did at CEC?) to help him build his case? I can't offer much in the way of a case study, but I am an Informix customer that has been doing an informal feasibility study on switching to PostgreSQL. I've learned a ton from the high quality PG docs and from the PG mailing lists. Both have their pros and cons. I haven't had equal hardware to compare PG performance, but my impression is that it is Informix is faster and has a more robust query planner. And by that, I mean that the Informix planner does a great job without things like data type casts and other help from the SQL developer. If I had to put a number on it, I would say that PG is 75 to 80% as fast as Informix. Please, don't anyone start a flame war over this, I'm just guessing here. With equal hardware and some more tuning, I'm sure I could do better with PG. Another edge for Informix is the fully synchronous replication feature where a committed transaction on the primary is guaranteed to be committed to disk on the secondary if you configure things appropriately. High availability is critical for us and that's been a weak spot for PG as far as I'm concerned. Yes, PG has replication, but in my opinion, it's not 'enterprise' class just yet. Lastly on the Informix side, they have more advanced online backup/restore tools. It's similar to PG PITR backup but does not depend on file-system level backup tools. The option I use (called ontape) stores data in a proprietary format to disk or tape. It also has an incremental backup option so you don't have to do a full dump every time. There is a more advanced Informix backup tool called onbar, but I haven't worked with it enough to comment on it. What does PG have going for it? Price, obviously. I'd love to have that $100k that I just spent back. PG has better conformance to SQL language standards, so portability of code would be easier. PG has some better built in functions and indexing features. I prefer the PLPGSQL language for stored procedures to Informix.PG has more options for stored procedure languages (python, perl). PG has table inheritance, Informix does not. One of the most impressive things about PG has been these mailing lists. Informix support is OK, but the front-line support drones just don't have the same access to developers who really know what's going on that you can get directly on this list. Heck, PG developers have answered my questions here on the weekend! I don't know if you can even put a price on such direct access to high-level gurus. I wish I had a better feature comparison list for you. I'm sure I've failed to mention a lot of great things about PG here, but like I said, my evaluation has been pretty informal. However at this time, I have concluded that we could move our company from Informix to PG without having to give up too much, other than the big licensing fees. We use a lot of open source software at our company and I would love to add PostgreSQL to the list. Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Using syslog on pg for Windows
On Jan 4, 2008 8:33 AM, Magnus Hagander <[EMAIL PROTECTED]> wrote: > On Wed, Jan 02, 2008 at 11:17:52PM -0800, Goboxe wrote: > > Hi, > > > > What are required to be configured in order to send logs to > > remote syslog server for pg running on Windows platform? > > > > In http://pgfouine.projects.postgresql.org/tutorial.html , it says > > "You can set syslog to send the log to another server through the > > network with @ip.ad.dr.ess." > > > > So try to update postgresql.conf > > > > syslog_facility = 'x.x.x.x' > > > > or > > > > syslog_facility = '@x.x.x.x' > > > > But it still does not work. > > > > Any tips? > > PostgreSQL on Win32 doesn't support syslog logging. You'll have to log to a > file and have an outside tool transfer that file to where youn eed it. PostgreSQL does, however, support logging to Windows Event Log. From there you could implement software to ship Event Log to remote syslog. -- Jeff ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
