Results 1 to 15 of 15

Thread: PostgreSQL FAQ

  1. #1
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Unanswered: PostgreSQL FAQ

    The following links, posts, and guidance should be helpful for you in obtaining timely and informative responses.

    1. The PostgreSQL Feature Matrix. Useful.
    2. Read the Manual !!! PostgreSQL comes with a HUGE manual that's fairly well organized. Also, make sure that the manual you're reading is the same as your installed version... (Note: The manual linked at #2 points to the current software release.)
    3. Post your questions in a clear, precise, and concise manner. Be sure to provide as much information as possible. Otherwise, we'll be forced to ask more questions to try and tease enough information from you, or we'll be providing "shotgun" type answers. In both cases, you're wasting time, and we're wasting time...
    4. Search the mailing list archives.. The odds are, your question has been asked there.
    5. Search this forum. The search button is a very handy tool.
    Last edited by loquin; 04-08-16 at 18:13.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    ODBC Connection Strings for pG

    It took me some time to determine a good connection string to connect to PostgreSQL. Yes, the DSN or the dotNet connections strings are pretty well defined at many sites, but I couldn't locate a good reference for a DSN-less connection string.

    After a lot of trial and error, I finally managed to get one that worked. Check out these; they work for me...

    Now, tracking down a connection string CAN be painful. Ref this tutorial in our sister site, Xtreme VB Talk, for a few approaches to creating that perfect connection string....

    If you prefer to download a finished utility, one based on the above code is available here.
    Last edited by loquin; 03-26-07 at 18:02.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    On his very first post, user tobymaguire has graciously posted a DB2 to PostgreSQL migration word document file on our site. Thank you for the site support, Toby!

    Migrating DB2 UDB to PostgreSQL

    Another good source of information when converting from DatabaseA to PostgreSQL (or, vice-versa, for that matter) is Kline's SQL in a Nutshell, Second edition, from O'Reilly.
    Last edited by loquin; 03-24-06 at 17:21.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    PostgreSQL Books

    A decent book, Practical PostgreSQL by John Worsley(O'Reilly) is available on-line. Although it was written for version 7.1x, many of the points made should remain valid with later editions of PostgreSQL.

    PostgreSQL, Second Edition, by Douglas & Douglas. (Sams) is a pretty useful addition to your library. It is broken into three sections, aimed at the three classes of users: The first section deals with using PostgreSQL, and includes Information on PostgreSQL's SQL Syntax and use, working with data, and performance. The second section is aimed at developers, and is primarily concerned with programming with postgreSQL, contains information on programming with PostgreSQLs existing languages, as well as extending PostgreSQL. Finally, the third section is aimed at PostgreSQL administration. Here is Chapter 3: PostgreSQL SQL Syntax and Use, in its entirety.

    SQL in a Nutshell, Second Edition, by Kline, Kline and Hunt (O'Reilly) is a comparitive SQL reference. It compares the various dialects of SQL, command-by-command for ANSI SQL, SQL Server, DB2, Oracle, PostgreSQL, and MySQL. There's also a separate appendix dealing with the differences between SQL Server and Sybase Adaptave Server (12.5) Note that this book makes no pretense at explaining the various SQL commands. It assumes that you know at least one of the SQL dialects, and need know the differences between the dialect that you know, and a different implementation.
    Last edited by loquin; 09-05-07 at 16:44.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    PostgreSQL versus DatabaseX

    A common request is "Which database is better? PostgreSQL or DatabaseX"

    Of course, the underlying problem is that most requestors don't post their definition of what constitutes "Better."

    I DO know that almost every application has different database requirements, and a feature that is of little or no consequence to ApplicationA may be of DIRE importance to ApplicationB.

    The plain and simple fact is that you can apply (with varying levels of work on your part) almost any of the serious database contenders in most applications, and make it work. However, some database ARE better suited for specific applications than are others, and the attached links try to clarify this.

    Note: I try not to get involved in these sorts of discussions, as they tend to quickly break down into fervent, almost religious arguments as to why "DatabaseA is better than DatabaseB."

    That being said, here are the current links...

    Last edited by loquin; 08-27-09 at 16:50.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Savepoints in PostgreSQL Transactions

    I ran across an interesting article, describing the advantages of PostgreSQL's new Savepoints feature, as well as how to implement them.

    PostgreSQL Version 8's Transaction Savepoints Can Save Your Data
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Peachpit.com, the parent publishing company of New Riders, has a very useful sample chapter from Douglas & Douglas' PostgreSQL oinline. Chapter Three, "PostgreSQL SQL Syntax and Use" discusses PostgreSQL specific syntax for creating/altering databases, tables, and indexes, as well as getting information about databases and Tables, and Transaction processing.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Some questions we see routinely:
    1. How do I install PostgreSQL on Windows?
    2. [Why]/[when]/Where ... with the Windows Installer?
    3. How do I install postgreSQL [without user input]/[using a silent install]/[using a script] ?
    4. How do I Convert from Database X to PostgreSQL?
    Last edited by loquin; 02-04-08 at 15:16.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I've just posted a book review on a sister-site that may be beneficial: PostgreSQL
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Now that you've gotten that database created and running, you find that you're needing to programatically list all the tables, functions, constraints and other database objects. How are you going to go about this?

    User quipo pointed out a nice tutorial outlining some of the various uses of PostgreSQL's Information Schema, which is a set of views into PostgreSQL's system tables.

    PostgreSQL INFORMATION_SCHEMA

    For complete information regarding the Information Schema, ref Chapter 31 in the PostgreSQL Documentation.
    Last edited by loquin; 11-03-06 at 00:39.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Performance Tuning PostgreSQL

    Frank Wiles has written a nice article on PostgreSQL performance Tuning at revsys.com

    In addition, here are a couple more:
    Tuning PostgreSQL for Performance
    PostgreSQL Hardware Performance Tuning
    Last edited by loquin; 01-16-07 at 13:51.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Replication

    Slony is the Russian name for multiple elephants. It is also a GBorg project for PostgreSQL master-slave replication.

    Slony Overview

    The Slony Project
    Attached Thumbnails Attached Thumbnails Slon_450x320.jpg  
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  13. #13
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    Wikipedia

    Wikipedia has a nice article describing PostgreSQL's features and capabilities.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  14. #14
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    The PostgreSQL OleDB Provider

    has a couple of warts. They're not showstoppers, but they are inconvenient. (The OleDB provider is still in beta release, and doesn't appear to be undergoing much in the way of active development at this time.)

    First, the provider does not support the ADO connection's OpenSchema method. Sure, you can always get the schema from either the Information_Schema views, or from the system tables, but, if you're trying to build a multi-platform app, it is inconvenient.

    Second. It does not support updates to Select Recordsets. It treats all SELECT recordsets as read-only. As of yet, I haven't checked to see if you could build a recordset programatically, connect to the table, and update. One workaround, of course, is to build SQL Inserts or Updates, (or stored functions) to write the data.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  15. #15
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848

    I uninstalled Postgres but DON'T have a current backup! How do I recover?



    With thanks to shammat, for his step-by-step directions, originally posted here.
    1. Make a backup of your current data directory
    2. Make sure you have a backup of your data directory
    3. Install Postgres vx.x again, overwriting the current data directory (did you make sure you have a backup?)
    4. Shut down the Postgres service
    5. Make sure the Postgres service has stopped
    6. Check that Postgres is not running
    7. Copy the backup of your data directory to the original location (make sure Postgres is not running when you do that)
    8. Start the postgres service again
    Now, you should have a working Postgres server, with data restored and current, as of the point in time that you uninstalled Postgres without a backup.
    Last edited by loquin; 10-11-10 at 15:56.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •