Обсуждение: basic questions: Postgres with yum on CentOS 5.1

Hello,

About a week ago I got a VPS from a web host. The VPS is running
CentOS 5.1. I'm a software developer, new to Postgres and only have
basic Unix admin skills.

I asked the web host to make sure that Postgres is installed. They
did this by running the following command:
yum -y install postgre postgre-server postgre-devel

They told that the command downloads and installs the RPMs from the
CentOS 5.1 repo. And they believe it would have added a postgre
user without login capabilities. When I attempted to 'adduser
postgre' I discovered that the user already exists. I have confirmed
that postgres is indeed available:
[chuck@vs191 ~]$ postgres --version
postgres (PostgreSQL) 8.1.9

Upgrade to PostgreSQL 8.2.5?
Before I start creating databases, it seems like I should up consider
upgrading to PostgreSQL 8.2.5. I do not know of specific features I
need in version 8.2.5, I just thought I should be using the latest
stable version. Is this a good idea or should I just use version
8.1.9? If 8.2.5 is recommended, could someone suggest the best
approach for upgrading? If this isn't fairly quick and easy, I may
need to do it later.

Start server and create three databases:
My top priority to start the database server and create three
databases (with UTF-8) for my rails app. I expect that this should be
straightforward. I have read parts of Chapters 1 and 16 in the
Postgres manual, but I still have a few questions.

Questions:
1. Using root, should I change the password for the postgre user with
'passwd postgre' (so that I know the password)? Do I need to know the
password?

2. Do I need to manually create the data directory with postgre as
the owner before running initdb? Such as:
sudo mkdir /usr/local/pgdata
sudo chown postgres /usr/local/pgdata
su - postgres

3. To ensure that the databases I create use UTF-8 do I merely use
the '-E UTF8' option with initdb? Do I need to be concerned with
locale settings such as lc-collate? My server appears to have a
default locale setting:
[chuck@vs191 ~]$ locale
LANG=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=

I have more questions about environment variables and starting the
database server at reboot, but for the time being I just need to
create three databases. I'll read the manual more before asking those
questions.

Thanks and happy new year,
Chuck

On Mon, 31 Dec 2007, Chuck wrote:

> I asked the web host to make sure that Postgres is installed. They did this
> by running the following command:
> yum -y install postgre postgre-server postgre-devel

I think the proper command was:
yum -y install postgresql postgresql-server postgresql-devel

> Upgrade to PostgreSQL 8.2.5?

No. As you're not a unix admin I'd recommend staying with
distribution provided postgresql version. Otherwise you'd have to
upgrade it manually, stay alert of security vulnerabilities etc.
Distribution provided Postgresql would be updated automatically, just
like any other program. Just make sure automatic updates are turned
on.

> 1. Using root, should I change the password for the postgre user with
> 'passwd postgre' (so that I know the password)? Do I need to know the
> password?

No. This user should not have a password, it should be blocked. By
default it is created blocked so don't touch it.

The user is "postgres" not "postgre", by the way.

> 2. Do I need to manually create the data directory with postgre as the
> owner before running initdb?

No. The init script will do this for you. Just use
    service postgresql start
but first you have to have locale properly set:

> 3. To ensure that the databases I create use UTF-8 do I merely use
> the '-E UTF8' option with initdb?
> [chuck@vs191 ~]$ locale
> LC_CTYPE="POSIX"
> LC_COLLATE="POSIX"

This is bad. Make sure you have
    LANG="en_US.UTF-8"
set in a file "/etc/sysconfig/i18n" and reboot.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

On Mon, Dec 31, 2007 at 02:39:02PM -0800, Chuck wrote:
> yum -y install postgre postgre-server postgre-devel

I assume that's all spelled "postgres".  But otherwise, ok.

> Upgrade to PostgreSQL 8.2.5?
> Before I start creating databases, it seems like I should up consider
> upgrading to PostgreSQL 8.2.5. I do not know of specific features I
> need in version 8.2.5, I just thought I should be using the latest
> stable version. Is this a good idea or should I just use version
> 8.1.9? If 8.2.5 is recommended, could someone suggest the best
> approach for upgrading? If this isn't fairly quick and easy, I may
> need to do it later.

I suspect your web hosting company will not install the non-packaged
binaries for the OS and distribution you're using.  In such a case, you're
stuck with whatever yum installs.  If they will install any RPM set, then
you can just download the RPMs provided by the project, which are IIRC
source RPMs intended to be built on the target OS.

One thing that is important to note is that usually major version upgrades
require a dump-and-restore.  That means a database outage.  You'll want to
factor that into what you're planning for.

8.3 is about to be released, BTW, which sort of means 8.1 is looking longer
in the tooth every day.  But there's no reason to suppose 8.1 support will
stop any time soon.

> Questions:
> 1. Using root, should I change the password for the postgre user with
> 'passwd postgre' (so that I know the password)? Do I need to know the
> password?

It's entirely possible that you don't need postgres even to have a login
shell -- the OS users and the PostgreSQL users are not strictly dependent on
one another, unless you're using "ident" authentication.  You also should
probably not do most database operations as superuser anyway.

> 2. Do I need to manually create the data directory with postgre as
> the owner before running initdb? Such as:

Unlikely, since usually your package manager did this for you.

> 3. To ensure that the databases I create use UTF-8 do I merely use
> the '-E UTF8' option with initdb? Do I need to be concerned with

Yes (and see below).  But be careful: you cannot change the encoding without
doing initdb again, note.  Be especially aware of the interaction between
encoding and locale

> LANG=
> LC_CTYPE="POSIX"

This (and the rest of it) is what you want when you do that initdb.

A

On Tue, Jan 01, 2008 at 06:05:32PM +0100, Tomasz Ostrowski wrote:
> > LC_CTYPE="POSIX"
> > LC_COLLATE="POSIX"
>
> This is bad. Make sure you have
>     LANG="en_US.UTF-8"
> set in a file "/etc/sysconfig/i18n" and reboot.

If you do it that way, be _very sure_ you understand the interactions of
locale and sorting, &c. in your database system.

A

Thanks for Tomasz and Andrew's responses (as well as one person's
response off the list). There was a lot of valuable information. I've
done some further reading in the postgres manual and I've exchanged a
few emails from my web host.

My web host recommends sticking with the CentOS repo packages for
simplicities sake. They said that I can use "yum update postgres
postgres-devel postgres-server" to upgrade when they release the new
version. This sounds fine to me. I'm not sure how to "make sure
automatic updates are turned on" as Tometzky recommended. Is that a
yum setting?

Since I have root access, I believe that I should open a root shell
use a commands like this: sudo -u postgres createdb myDB

### background:
Since I have a packaged installation, I don't believe that I will
call initdb directly. I believe the following commands the proper way
to start and stop my database server:
#start server
service postgresql start
#stop server
service postgresql stop

I executed 'service postgresql start' and got the following output:
[root@vs191 ~]# service postgresql start
Initializing database:                   [ OK ]
Starting postgresql service:                [ OK ]

When I listed the current databases, I found out that UTF-8 is not being used.
[root@vs191 ~]# sudo -u postgres psql -l
could not change directory to "/root"
     List of databases
   Name  | Owner  | Encoding
-----------+----------+-----------
postgres | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)

I created a test database and confirmed that it's created with
'SQL_ASCII' encoding.
[root@vs191 ~]# sudo -u postgres createdb myTest
could not change directory to "/root"
CREATE DATABASE
[root@vs191 ~]# sudo -u postgres psql -l
could not change directory to "/root"
     List of databases
   Name  | Owner  | Encoding
-----------+----------+-----------
myTest  | postgres | SQL_ASCII
postgres | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(4 rows)

I found the installation location: /var/lib/pgsql/

### more info:
I need to store multiple languages in my database such as English,
French and Japanese.

The end of the "21.2.2. Setting the Character Set" section says, "One
way to use multiple encodings safely is to set the locale to C or
POSIX during initdb, thus disabling any real locale awareness."
http://www.postgresql.org/docs/8.1/interactive/multibyte.html

Without a package manager, I believe that this would be my initdb command:
sudo -u postgres initdb -D /var/lib/pgsql/data -E UTF8 --no-locale

I found the '--no-locale' option from 'initdb --help' and from this link:
http://docs.planetargon.com/PostgreSQL_Installation
I couldn't find the '--no-locale' option in the documentation.

### main question:
I think that need to figure where initdb is being called from to
modify its parameters. Or, I need to determine how to set the default
encoding to be UTF8. This might be more of a yum package question.

Thanks for your help,
Chuck

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 1 Jan 2008 18:49:40 -0800
Chuck <chuckr@velofish.com> wrote:

> ### main question:
> I think that need to figure where initdb is being called from to 
> modify its parameters. Or, I need to determine how to set the default 
> encoding to be UTF8. This might be more of a yum package question.

LANG="en_US.UTF-8" initdb -D foo

Should do it.

Joshua D. Drake



> 
> Thanks for your help,
> Chuck
> 
> ---------------------------(end of
> broadcast)--------------------------- TIP 6: explain analyze is your
> friend
> 


- -- 
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ 
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHev9YATb/zqfZUUQRAnixAJ0RAT3uEi6qbIr39wXyTNMJopB2ZgCgqBjH
B0lo9rjxmcpPOziT1rXkb4c=
=3v9K
-----END PGP SIGNATURE-----

On Tue, 1 Jan 2008, Chuck wrote:

> Since I have a packaged installation, I don't believe that I will call
> initdb directly.

There is actually another command I don't think you've noticed yet:

service postgresql initdb

That runs initdb with the user and permissions properly to get you
something the serivce can start and stop.  If you start the service
without an initialized database as you do in your example, it will run
that initdb task for you.

I'd suggest taking a look at the init script in
/etc/rc.d/init.d/postgresql for a bit to see what those commands are
actually doing under the hood.  The initdb one may not be useful for
you--the --auth settings may not be what you want for example, and there's
the language question as well.  But seeing exactly what the stock initdb
does should be instructive.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith@gregsmith.com> writes:
> On Tue, 1 Jan 2008, Chuck wrote:
>> Since I have a packaged installation, I don't believe that I will call
>> initdb directly.

> There is actually another command I don't think you've noticed yet:

> service postgresql initdb

Actually, that was only added in very recent versions of the postgresql
init script.  Before about 8.2, the first 'service postgresql start'
would automatically run initdb.  We split it out because of the (small)
risk of doing the wrong thing ...

In any case, I concur with the suggestion that you should use the init
script rather than invoking initdb directly.  In most package setups
that's much more likely to get the details right.

            regards, tom lane

Along with the other good remarks people have made, I want to point
something out.

On Tue, Jan 01, 2008 at 06:49:40PM -0800, Chuck wrote:
>
> I created a test database and confirmed that it's created with
> 'SQL_ASCII' encoding.
> [root@vs191 ~]# sudo -u postgres createdb myTest
> could not change directory to "/root"
> CREATE DATABASE

There are two issues above worth noting.  First, by doing this as user
postgres, you're creating a database _owned by postgres_.  There's nothing
fundamentally wrong with that, but I think it generally a good practice to
reduce as much as possible the reliance on the superuser.  You can create
other accounts.  See the sections of the manual on users.

Second, that "could not change directory to '/root'" tells me that your
postgres user really is, as already suggested upthread, not intended by your
package maintainer to be used with any regularity.  It has no home
directory.

> Without a package manager, I believe that this would be my initdb command:
> sudo -u postgres initdb -D /var/lib/pgsql/data -E UTF8 --no-locale
>
> I found the '--no-locale' option from 'initdb --help' and from this link:
> http://docs.planetargon.com/PostgreSQL_Installation
> I couldn't find the '--no-locale' option in the documentation.

Yes, that should work.  But see the other posts for suggestions on better
ways to deal with this.

A

On Tue, 01 Jan 2008, Chuck wrote:

> I'm not sure how to "make sure automatic updates are turned on" as
> Tometzky recommended. Is that a yum setting?

You need to install and configure "yum-updatesd" to perform automatic
updates for you. I don't use it so I don't know exactly how to do
this, but I believe it has a well documented configuration file in
/etc/.

> [root@vs191 ~]# service postgresql start
> Initializing database:                   [ OK ]
> When I listed the current databases, I found out that UTF-8 is not being used.

You did not set your /etc/sysconfig/i18n and reboot before you first
started, ignoring my recommendation. I'd delete /var/lib/pgsql/data
(if there's no data yet) and try again after this setting and reboot.

> I need to store multiple languages in my database such as English, French
>
> The end of the "21.2.2. Setting the Character Set" section says, "One way
> to use multiple encodings safely is to set the locale to C or POSIX during
> initdb, thus disabling any real locale awareness."

This is a very bad solution, as it would allow you to store any
garbage string in a database. It won't know letter boundaries, so any
text functions will misbehave badly. When your database encoding is
UTF-8 then you'll be forced to save consistant UTF-8 strings and
sorting, text functions, regular expressions etc... will work as
expected.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

I'm sorry for my delayed response. Tomasz, thanks for your email.

At 2:38 PM +0100 1/3/08, Tomasz Ostrowski wrote:
>On Tue, 01 Jan 2008, Chuck wrote:
>
>>  I'm not sure how to "make sure automatic updates are turned on" as
>>  Tometzky recommended. Is that a yum setting?
>
>You need to install and configure "yum-updatesd" to perform automatic
>updates for you. I don't use it so I don't know exactly how to do
>this, but I believe it has a well documented configuration file in
>/etc/.

I'll look into this further. Thanks.

>  > [root@vs191 ~]# service postgresql start
>>  Initializing database:                   [ OK ]
>>  When I listed the current databases, I found out that UTF-8 is not
>>being used.
>
>You did not set your /etc/sysconfig/i18n and reboot before you first
>started, ignoring my recommendation. I'd delete /var/lib/pgsql/data
>(if there's no data yet) and try again after this setting and reboot.

Since I had sent this email, I contacted my web host for help. They
said that I could '-E UTF8 --no-locale' to the initdb call within
/etc/init.db/postgresql. I stopped postgres, deleted the data
directory and restarted postgres. My cluster was now using UTF-8:

bash-3.1$ psql -l
            List of databases
        Name       |  Owner   | Encoding
------------------+----------+----------
  postgres         | postgres | UTF8
  template0        | postgres | UTF8
  template1        | postgres | UTF8
(6 rows)

I used 'createdb myTest' to create new database with that uses UTF-8.

My main concern was to set the encoding to UTF-8. I knew that was
important. I believe that I did that with the '-E UTF8' option for
initdb. Sort order, and specifically setting LC_COLLATE and LC_CTYPE
was less of a concern. (I still need to read and learn more.)

Fortunately, I expect to be able to delete my /var/lib/pgsql/data
directory for the next few weeks, if necessary. I wanted to
investigate your recommendation further before accepting it.

By the way, do you think that specifying '--locale=en_US.UTF-8' for
initdb id equivalent to having LANG="en_US.UTF-8" set in the
"/etc/sysconfig/i18n" file (and rebooting)?

>  > I need to store multiple languages in my database such as English, French
>>
>  > The end of the "21.2.2. Setting the Character Set" section says, "One way
>  > to use multiple encodings safely is to set the locale to C or POSIX during
>  > initdb, thus disabling any real locale awareness."
>
>This is a very bad solution, as it would allow you to store any
>garbage string in a database. It won't know letter boundaries, so any
>text functions will misbehave badly. When your database encoding is
>UTF-8 then you'll be forced to save consistant UTF-8 strings and
>sorting, text functions, regular expressions etc... will work as
>expected.

I agree with you that enforcing a database encoding of UTF-8 is a
good approach. I believe that I'm doing that.

If I'm storing multiple languages such as English, French and
Japanese do I really want to specify an English locale for English
sorting only (which will affect indexes)? If I have multiple
languages and must pick one locale for Postgres, is no locale with
(with UTF-8 encoding) acceptable?

This reference at the end of the "21.2.2. Setting the Character Set"
section in the 8.1 manual still makes sense to me:
    Important: Although you can specify any encoding you want for a
database, it is unwise to choose an encoding that is not what is
expected by the locale you have selected. The LC_COLLATE and LC_CTYPE
settings imply a particular encoding, and locale-dependent operations
(such as sorting) are likely to misinterpret data that is in an
incompatible encoding.
    Since these locale settings are frozen by initdb, the apparent
flexibility to use different encodings in different databases of a
cluster is more theoretical than real. It is likely that these
mechanisms will be revisited in future versions of PostgreSQL.
    One way to use multiple encodings safely is to set the locale to C
or POSIX during initdb, thus disabling any real locale awareness.
http://www.postgresql.org/docs/8.1/static/multibyte.html

Am I on the right track? Any thoughts would be appreciated.

Thanks,
Chuck

On Sun, 06 Jan 2008, Chuck wrote:

> Sort order, and specifically setting LC_COLLATE and LC_CTYPE was less of a
> concern. (I still need to read and learn more.)

It should be, as it is not only sort order. Try for example this:

select upper('ąŧäɣ');
  - these are
    polish a_ogonek, t stroke, german a umlaut and greek gamma

For example in en_US.UTF-8 locale:
 upper
-------
 ĄŦÄƔ

But in C locale:
 upper
-------
 ąŧäɣ

This will also affect for example case independent pattern matching.

> By the way, do you think that specifying '--locale=en_US.UTF-8' for
> initdb id equivalent to having LANG="en_US.UTF-8" set in the
> "/etc/sysconfig/i18n" file (and rebooting)?

For PostgreSQL yes. But I'd recommend UTF-8 for the whole system.

> If I have multiple languages and must pick one locale for Postgres,
> is no locale with (with UTF-8 encoding) acceptable?

I think it is. If this upper/lower problem is not a concern to you.
But I think it is strange and I don't think it will buy you any real
performance improvements.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

On Jan 7, 2008 1:28 AM, Chuck <chuckr@velofish.com> wrote:
> Since I had sent this email, I contacted my web host for help. They
> said that I could '-E UTF8 --no-locale' to the initdb call within
> /etc/init.db/postgresql. I stopped postgres, deleted the data
> directory and restarted postgres. My cluster was now using UTF-8:

Please note however, that individual database encoding can be set at
the time that the database is created, so you don't have to re-initdb
to do that.  I.e.:

create database mydb with encoding 'UTF8';
create database yourdb with encoding 'SQLASCII';
\l
 mydb                      | smarlowe             | UTF8
 yourdb                    | smarlowe             | SQL_ASCII

The only thing you should need to reinitdb for is locale.