Archive for the ‘PostgreSQL’ Category

Change PostgreSQL Locale

Friday, January 21, 2011 posted by rafael

wrote by Rafael Marangoni, from Suporte Linux team.

This article explains how to change the default PostgreSQL Locale. Be careful, because we’re considering that you do not have data on postgresql instance.

First, we need to stop the postgres service (if was not already stopped):

/etc/init.d/postgresql stop

Then we need to remove all data files from postgresql data dir. On RedHat based, that’s the dir:

rm -rf /var/lib/pgsql/data/*

Now we change to postgres user:

su postgres

Creating the initial data, selecting the Locale:

initdb –locale=C /var/lib/pgsql/data

or

initdb –locale=pt_BR.UTF-8 /var/lib/pgsql/data

or your locale

/etc/init.d/postgresql stop

Then, we start postgres service:

/etc/init.d/postgresql start

wrote by Rafael Marangoni, from Consultoria Linux team.

By default, on some distros, PostgreSQL will only accept connections from localhost. When you have only access from localhost (from localhost Apache, by example) everything is ok, but when you need that postgresql accepts connections for other hosts, you need to make some configs.

First of all, edit the postgresql.conf file (on CentOS the default location is /var/lib/pgsql/data/postgresql.conf).

vi /var/lib/pgsql/data/postgresql.conf

Search the following line:

listen_addresses = ‘localhost’

Change it to:

listen_addresses = ‘*’

Secondly, you need to change the permissions inside pg_hba.conf file (on CentOS, the default location is /var/lib/pgsql/data/pg_hba.conf)

vi /var/lib/pgsql/data/pg_hba.conf

Include the following line (at the end of the file):

host    username    all         192.168.0.10/32     md5

Where:
username: it’s the name of the postgres user

all: the database name (here we enabled all of them)
192.168.0.10/32: is the IP address/subnet to accept connections
md5: is the method of authentication (md5 requests password)