PostgreSQL

Installation for RedHat Enterprise Linux 5

#1. (as root) # yum install postgresql

#2 Start the service: (as root) # service postgresql start

NOTE: This will create all the files you need for the postgresql service!!

#3. Make sure the service starts on reboot: (as root) # chkconfig postgresql on

Configuration

#1. Change to the postgresql user: su – postgres

#2. Enter into the postgresql command shell: psql

#3. Set the postgresql password for the postgres user from within the postgres shell:  alter user postgres with password ‘newpassword’; OR ALTER USER username ENCRYPTED password ‘newpassword’; The latter will create an MD5 has of the password.

#4. List the default databases from within the postgresql shell: postgres =#\l

OUTPUT: List of databases

Name | Owner | Encoding

———–+———-+———-

postgres | postgres | UTF8

template0 | postgres | UTF8

template1 | postgres | UTF8

(3 rows)

pg_hba.conf File

#1. TYPE DATABASE USER CIDR-ADDRESS METHOD

# “local” is for Unix domain socket connections only

# local all all ident sameuser local all all ”’md5”’

# IPv4 local connections: host all all 127.0.0.1/32 ”’md5”’

#host all all 127.0.0.1/32 indent sameuser

# IPv6 local connections: host all all ::1/128 ident sameuser

Create a new user

* From the postgresql shell: create user username with password ‘newpass’;

OUTPUT: CREATE ROLE

* Edit user to superuser and allow user to create databases: ALTER ROLE username WITH CREATEDB SUPERUSER CREATEROLE;

Create a new database

* From the postgresql shell: create database databaseName;

OUTPUT: CREATE DATABASE

* Create a database with a specific character set encoding: create database databaseName with encoding ‘SQL_ASCII’; OR create database databaseName with encoding ‘utf8’;

* You can find additional character set encodings here: http://www.postgresql.org/docs/8.3/static/multibyte.html

Grant privileges for new user on new database

* From the postgresql shell: grant all privileges on database databaseName to username;

OUTPUT: GRANT

Test connection

* Enter into a linux shell with the user account create above. su – username

* Connect to the database as that user: psql -d databaseName -U dlopez

Moving over the Databases

Dump the Databases

* On the existing server dump all the databases: pg_dumpall > all.dbs.out

On the new server import the out file: ./psql -f /path/to/all.dbs.out postgres

* Or you can just dump a single database: pg_dump databaseName > databaseName.pgsql

Insert the Databases

* On the new server

#1. Create UNIX system account username and set the password to something random.

#2. Create a postgres account username and give it superadmin and createdb privileges. From within the postgresql shell as the postgres user:

CREATE ROLE username WITH PASSWORD ‘password’;

ALTER ROLE username WITH CREATEDB SUPERUSER CREATEROLE;

#3. Exit postgresql shell as postgres user: \q

#4. Su to the username UNIX system account: su – username

#5. Create the databases necessary. Connect to postgresql shell with username account

psql -d databaseName

From within the postgresql shell create the databases: create database databaseName with encoding ‘utf8’;

# Exit the postgresql shell: \q

# Populate the databases from the pg_dumps taken from the old server. From the unix command line as username: psql databaseName < databaseName.psql

Troubleshooting

Unable to create database SQL_ASCII

My postgresql 8.4 database was created with all tables having UTF8 as encoding, probably because I use an UTF8-locale. Now a particular database needs to use SQL_ASCII instead of UTF8 and this is correctly specified in the postinst script. The problem is that postgresql cannot create the database with a different encoding (SQL_ASCII vs. UTF8) as long as you do not specify -T template0:

————————————————————————

postgres $ createdb -O owner -E SQL_ASCII database createdb:

database creation failed: ERROR: new encoding (SQL_ASCII) is incompatible with the encoding of the template database (UTF8) HINT: Use the same encoding as in the template database, or use template0 as template.

postgres $ createdb -O owner -E SQL_ASCII -T template0 database

postgres $ psql

postgres=# \l

List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges

———–+———-+———–+————-+————-+———————–

database | owner | SQL_ASCII | de_DE.UTF-8 | de_DE.UTF-8 |

postgres | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |

template0 | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres : postgres=CTc/postgres

template1 | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres : postgres=CTc/postgres (4 rows)

————————————————————————

So, please add this flag to the creation.

psql sorry too many clients

Solution was taken from here: http://www.cyberciti.biz/faq/sorry-too-many-clients-when-trying-to-connect-to-postgresql-database-server-solution/

Edit /var/lib/pgsql/data/postgres.conf and change max_connections from 100 to 300

Restart the service: service postgresql restart

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s