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 .

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:

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

Should be:

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

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

Thanks a lot for the guide !

[PaveQ]

Thanks, thats great!

[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.

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/ ... -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]

Code: Select all

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

Should be:

Code: Select all

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

Code: Select all

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

Should be:

Code: Select all

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

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/or ... ries4a.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.

[jmanko]

I started to follow this tutorial, but I get:

Code: Select all

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

Code: Select all

# /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) :

Code: Select all

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]

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:

Code: Select all

/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

Code: Select all

createdb -U postgres -W test

and receive the same error

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