# PostgreSQL Guide Feedback

## ChrisWhite

Hey all,

I did a guide on PostgreSQL setup located here:

http://www.gentoo.org/doc/en/postgres-howto.xml

and would like feedback on it. As with everything else, I can't guarantee I'll accept everyone's sugestions,

but I'll sure be appreciative  :Smile: .

Anyways, I'm mainly doing this as it's a draft, and I need to see how people expect to use it.

Thanks to everyone!

Mod edit: Stuck for a while,  --kallamej

Unstuck. -- desultory

----------

## davidblewett

Wow, I wish this had been available a few months ago when I was getting into Postgres for the first time on Gentoo. Anyway, thanks!

One nitpick:

In the USE flag section this line:

 *Quote:*   

> nls  	 If this option is enabled, PostgreSQL can utilized translated strings for non-English speaking users.

 

Should be:

 *Quote:*   

> nls  	 If this option is enabled, PostgreSQL can utilize translated strings for non-English speaking users.

 

----------

## ChrisWhite

ack, thanks for catching that, I'll toss it to the next docs person I see.

----------

## Ateo

The guide works well. Thanks!!

----------

## giant

Nice overview  :Smile: 

Had no problem installing a nice little postgres server with it !

Thanks a lot for the guide !

----------

## PaveQ

Thanks, thats great!   :Very Happy: 

----------

## beandog

Just a couple of things.

First off, I wouldnt start off the introduction with a comparison / rip on MySQL.  I agree with you 100% of course, but I'd take a line from this instead.

Another thing, I would change this:

"To start out, let's create a test database by using the createdb command. We'll also pass along the -U option to set the user (it defaults to the current user name if you don't), ..."

I'd change "the current user name" part to something like "the current user logged in (root, for example)."  Something along those lines to distinguish that you're talking about system users, not psql users.

Last thing, it might be kind of nice to throw in some environment variables that can be set, such as PG_USER, so I don't have to pass -U each time.  :Smile: 

Looking good, though.

----------

## kashani

You've got the exact same issues as your Mysql How-to, near total lack of Gentoo specific info.

Things that would actually fix user probems before they have them:

1. Discussion of config files. /var/lib/postgressql/pg_hba.conf and /etc/conf.d/postgresql should be mentioned and the user might need to touch these to get what they want.

2. Bound to localhost by default.  Gentoo default is to bind database to localhost by default. THe guide should explain that and how to change. If you're feeling like an overachiever you can even discuss IP and username ACL's

3. How to delete the database and start from scratch. Every user screws up their db and want to start from scratch or at least from a point where they can get back into their database.

There are a thousand plus places I can go to learn how to insert into Postgres and hardly any that has reliable Gentoo specific info about how Postgres was installed, where the config files are, and some of the default settings. The Gentoo docs should cover the Gentoo specific cases before doing anything else.

kashani

----------

## Mad Merlin

Foreign keys have been supported in MySQL for InnoDB tables since version 3.23. Some things that were added in MySQL 5 were views, stored procedures and triggers.

You're free to bash MySQL if you want to, but please get your facts straight. See here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html

----------

## castaway

You might want to mention the library-only package, "libpq" in there somewhere..

This is what I went there looking for, and ended up asking on #gentoo instead ;)

C.

----------

## mkb137

I followed the guide and liked it.

After setting up all those users and passwords, I was suprised to find that the database would accept my connections and changes no matter what password I gave.  After some other research I found out why : the "trust" option in pg_hba.conf.  I don't know if want to mention that in your guide or not.

----------

## Wicked Wesley

The guide looks good!

Maybe you should add a note about PGAdmin, in my opinion a nice PostgreSQL application, which is available in Portage, dev-db/pgadmin3.

Keep up the good work,

Wesley

----------

## Ateo

```
$ createuser -a -d -P -E -U postgres -W chris
```

Should be:

```
$ createuser -s -d -P -E -U postgres -W chris
```

```
$ createuser -A -D -P -E -U postgres -W chris
```

Should be:

```
$ createuser -S -d -P -E -U postgres -W chris
```

 *Quote:*   

> shadow ~ # createuser --help |grep superuser
> 
>   -s, --superuser           role will be superuser
> 
>   -S, --no-superuser        role will not be superuser
> ...

 

----------

## dec

Ideas/suggestions:

Backups - briefly mention pgdump and online/PITR backups - having a great database is useless without backups!

More postgresql.org references - the documentation on postgresql.org is fantastic and should be mentioned more often than just the very last sentence.

Performance - out of the box postgres can be a little sluggish on a large DB, point everyone to the performance guide on powerpostgresql.com

Tom

----------

## intgr

Note that the 'Oracle Reference Guide' link in the description of the 'pg-hier' USE flag is broken, you seem to have inserted spaces into it.

The correct link is http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/queries4a.htm

----------

## GoofballJM1

Although it's not as severe as with MySQL, but an upgrade guide for Postgres would be very helpful.  Having information regarding that will save many of us novice Postgres users a little time from scrounging through the forums and on google.  :Wink: 

----------

## jmanko

I started to follow this tutorial, but I get:

```

createuser: could not connect to database postgres: FATAL:  could not open file "global/pg_database": No such file or directory

```

I also notice I keep getting a *.pid message when restarting Postgresql

```
# /etc/init.d/postgresql restart

 * Stopping PostgreSQL ...

No process in pidfile `/var/lib/postgresql/data/postmaster.pid' found running; none killed.                                                         [ ok ]

 * Starting PostgreSQL ...  

```

Any ideas?

----------

## _pF_

The emerge --config  creates by default b0rked databases that don't support utf8.  I know it's not yet supported, but the 8.2 ebuild doesn't have an emerge --config at all.

----------

## Frelon

Hi,

I have also the error when trying to create a db (sorry for french output) :

```

stan@hermes ~/Bommings/bommings-dev $ createdb -U postgres -W test

Mot de passe : 

createdb: échec lors de la connexion à la base de données postgres: FATAL:  n'a pas pu ouvrir le fichier «global/pg_database» : Aucun fichier ou répertoire de ce type

```

I followed the howto, change postgres user passwd etc.

Thx !

EDIT : I fixed that in killing -9 the postgres process rather than /etc/init.d/postgresql restart which restart nothing !!!

----------

## kashani

 *GoofballJM1 wrote:*   

> Although it's not as severe as with MySQL, but an upgrade guide for Postgres would be very helpful.  Having information regarding that will save many of us novice Postgres users a little time from scrounging through the forums and on google. 

 

heh. Obviously you've never tried to update from Postgres 7.2 to 7.4. Or from 7.4 to 8.0. Bloody nightmare if you've got databases of any complexity. On top of all that Postgres can not replicate between different versions so database updates of Postgres always require extensive downtime rather than the Mysql way of updating a slave and then promoting it to master. Some downtime is required, but you're not waiting for 40 minute imports of your database dumps to finish.

I'd say there definitely needs to be a Postgres update guide even though things have been relatively calm in the 8.0+ versions.

kashani

----------

## Joseph_sys

Maybe somebody can help me here.

After upgrading several perl scripts yesterda, when I try to log-in to sql-ledger I get an error:

apache log:

```
/usr/local/sql-ledger/login.pl: symbol lookup error: /usr/lib/perl5/vendor_perl/5.8.8/i686-linux/auto/DBD/Pg/Pg.so: undefined symbol: PQserverVersion

[client 127.0.0.1] Premature end of script headers: login.pl
```

Can anybody point me in the right direction?

----------

## Highlands

how about adding a guide on doing backup and/or doing a upgrade?

----------

## lomom

wow nice !

----------

## YuriyRusinov

After updating to postgresql-8.2.4-r1 I try to do

```

createdb -U postgres -W test

```

and receive the same error

 *Quote:*   

> 
> 
> createdb: could not connect to database postgres: FATAL:  could not open file "global/pg_database"
> 
> 

 

Thanks for attention in advance.

----------

## mudrii

Big thank for Postgresql documentation I rely needed

----------

## zeek

 *Mad Merlin wrote:*   

> Foreign keys have been supported in MySQL for InnoDB tables since version 3.23. Some things that were added in MySQL 5 were views, stored procedures and triggers.
> 
> You're free to bash MySQL if you want to, but please get your facts straight. See here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html

 

Its been over 1 year and this still hasn't been addressed?

Stuff like this is one of the reasons I think Postgres isn't taking off.  Pg zealots seem to be fixated on ancient problems with mysql and wont acknowledge that they've been addressed.  The result is people think "raving lunatic" when they think about Postgres.

The cheap shot about mysql and foreign keys only being supported started with mysql 5 needs to be removed from the guide.  Its completely bogus.  The bogus sentence is (engrish alert):

 *Quote:*   

> Most of the standard features such as FOREIGN KEY was only just added in MySQL 5.

 

----------

## tSp

 *Joseph_sys wrote:*   

> Maybe somebody can help me here.
> 
> After upgrading several perl scripts yesterda, when I try to log-in to sql-ledger I get an error:
> 
> apache log:
> ...

 

end of script headers problem is here - http://www.maysville-linux-users-group.org/ftopict-30.html  Different distro, but same fix

----------

## ucntcme

 *zeek wrote:*   

>  *Mad Merlin wrote:*   Foreign keys have been supported in MySQL for InnoDB tables since version 3.23. Some things that were added in MySQL 5 were views, stored procedures and triggers.
> 
> You're free to bash MySQL if you want to, but please get your facts straight. See here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html 
> 
> Its been over 1 year and this still hasn't been addressed?
> ...

 

While I would remove any comparison of MySQL and PostgreSQL in a user guide to either of them neither your statement nor the one in the aforementioned doc is properly accurate. By default, last I knew, InnoDB was not default (i.e. standard) storage engine, and as that is the only place foreign keys were supported (every else it just silently ignores which IMO is the worst failing of MySQL - it's tendency to not tell you things like that). As such IF I were force to include the reference I would make it clear that while MyISAM will accept the FOREIGN KEY declaration it will silently ignore it and that you need to use InnoDB if you need actual support. I know of many people that got bit by taking statements like yours as MySQL supporting transaction and doing their code that way without realizing that others may not have their DB set to use InnoDB and then encounter user problems because the install of the application was on an MySQL server that was not using InnoDB. IMO to say that pre-5 MySQL support foreign keys is like saying PostgreSQL supports replication. Neither of them were/are standard, nor meet the expectations of saying they are (like foreign keys on temp tables not working in MySQL, and significant deviation for the SQL standard re: foreign keys on MySQL).

But like I said, comparisons do not belong in virtually any user guide.

Things like is one of the reasons PostgreSQL *is* becoming increasingly popular as people get bit by MySQL "choices" (especially at the large corps and financial firms I deal with). This is just one item among many. PG has it's issues (can you say DB upgrades?) but I'd prefer to have them then data getting silently changed under my feet because the DB assumes your first time stamp field on a table is something it should update to the current time every time it modifies that entry.

As far as zealotry, I see far, far more of that from MySQL users. I've never gotten a glare from a PostgreSQL proponent by suggesting MySQL. A raised eyebrow or two, sure. But never a glare or a glare followed by a lecture about how MySQL is "soo much faster" (again not true they are pretty close to the same), or outright hostility. I can't say the reverse however.

So how do we get the opening bits of this doc fixed (or anything else that needs updated). If it's been a year between these comments, whatever method was tried before has not worked.

----------

## zeek

 *ucntcme wrote:*   

> While I would remove any comparison of MySQL and PostgreSQL in a user guide to either of them neither your statement nor the one in the aforementioned doc is properly accurate. By default, last I knew, InnoDB was not default (i.e. standard) storage engine...

 

It seems another Postgres Zealot looks past the truth ... Lets review the original again shall we?

 *PostgreSQL Guide Feedback wrote:*   

> Most of the standard features such as FOREIGN KEY was only just added in MySQL 5.

 

There is no claim to what the "default storage engine" is, only to the support of the feature.

----------

## uprooter

One word !

Thanks.

----------

## pactoo

Maybe you could say a word to i18n and creating a database with a certain encoding or locale and its effects and consequences. I currently do not understand this myself, but in case somebody answers my question, you may might want to add this information

https://forums.gentoo.org/viewtopic-t-649259.html

----------

## Eckos

Thank you so very much. Too bad we can't get alot of users to switch to postgres from mysql   :Laughing: 

----------

## salmonix

Helpful, indeed.

However, I would drop the PostgreSQL SQL intro - the PostgreSQL manual online - http://www.postgresql.org/docs/8.0/interactive/ddl.html - covers the topic a bit more in details.

It would however be more interesting to call attention to the scripting possibilities of Pg, the inheritance, special datatypes and some notes on tweaking and probably schemas.

Also the external projects are interesting - as I recall there is a GPS related extension around.

IMHO, 'course.

----------

