Gentoo Wiki ArchivesGentoo Wiki

PostgreSQL/Install

Contents

Installation and Setup

Before Emerging

Make sure that the "postgres" is in the USE variable in /etc/make.conf . This will ensure that future programs you emerge will be built with postgresql support installed (like php for instance). At least make sure it is your USE variable when emerging postgresql.

Then because of the large community development of postgresql is going steadily. Recently some significant changes have been implemented, making the latest 8.x.x versions unfortunately not always compatible with previous ones. Of course this doesn't affect people with a fresh install but might cause trouble when migrating from a previous version. The best way around this is to dump your postgresql data into a file before proceeding. The information how to do just that is in the section 'Upgrading Postgresql'.

Emerging

Get the latest stable postgresql with

# emerge -av postgresql

If you want the latest ebuild, find the latest postgresql ebuild. I got the latest version from gentoo-portage.com. You can always check for new versions on the postgresql website. For more information about installing custom ebuilds, see HOWTO Installing 3rd Party Ebuilds. After you got the ebuild, we have to unmask the package:

# nano -w /etc/portage/package.keywords

Add the following packages, replacing the .x.x with your version:

Code: package.keywords
~dev-db/postgresql-8.x.x
~dev-db/libpq-8.x.x

This will unmask the postgresql ebuild. Now run

# emerge -av postgresql

to emerge your latest postgresql version.

Configuring

Time to edit some of the configuration files. Tons and tons of options are available which I will not go through. I will only go through the most obvious/important ones. Feel free to add any options which YOU feel are important and are not mentioned here ... That is why it is a wiki :D

Main Configuration

After emerging you need to create a place where the databases and the configuration files are stored. By default you can use the gentoo default configuration:

# emerge --config =postgresql-8.x.x  //Replace "8.x.x" with correct version number

If the configurations fails with a message like

failed to initialize lc_messages to ""

set up your LC_MESSAGE locale variable. You might use

# export LC_MESSAGES="en_EN.utf8"

for English locales.

This will install the config files and database files into : /var/lib/postgresql/data . If you want the config and data files somewhere else here is an example of how to use the directory : /data/postgresql/data

#  mkdir -p /data/postgresql/data
#  chown -R postgres:postgres /data/postgresql/data
#  su - postgres
#  initdb -D /data/postgresql/data

Then change the PGDATA to the appropriate directory

# nano -w /etc/conf.d/postgresql
Code: /etc/conf.d/postgresql
 
  PGDATA="/data/postgresql/data"
  

You probably want to comment out the PGOPTS="-N 40 -B 80" line and either go with what the initdb defaults to in postgresql.conf, or set your own ( the PGOPTS="-N 40 -B 80" sets max connections to 40 and shared_buffers to 640k -- the documentation says that several tens of MB are required for production servers and that the minimum must be the greater of 128 kilobytes or #connections * 16 kilobytes. The initdb on my system defaulted these two values in postgresql.conf to 100 connections and 24MB of shared_buffers) Since lots of additional programs, that depend on or work with postgresql, expect the postgresql configuration files to be located in '/var/lib/postgresql/data' I will create a symbolic link so they work as well. This will save you lots of configuration in the future.

# mkdir -p /var/lib/postgresql
# ln -s /data/postgresql/data/ /var/lib/postgresql/

For '/etc/' Configuration users

Postgresql puts the .conf files into the PGDATA directory instead of '/etc'. For users that are used to edit their configuration files in '/etc' this will help them out.

# mkdir /etc/postgresql

For default

# ln -s /var/lib/postgresql/data/pg_hba.conf /etc/postgresql/pg_hba.conf
# ln -s /var/lib/postgresql/data/postgresql.conf /etc/postgresql/postgresql.conf

For changed PGDATA dir

# ln -s /data/postgresql/data/pg_hba.conf /etc/postgresql/pg_hba.conf
# ln -s /data/postgresql/data/postgresql.conf /etc/postgresql/postgresql.conf

(Note for changed PGDATA users who have created a symbolic link it doesn't matter which one they use)

Settings External Connections

By default postgresql ONLY accepts local connection in other words it accepts only connections to "localhost". This is the most secure option if all your the database connections originates from the same host as you run postgresql on. But if you have postgresql running on an external host here is how to make postgresql listen for external connections :

Open the postgresql.conf file

 # nano -w  /var/lib/postgresql/data/postgresql.conf

Uncomment the listen_addresses option and add the allowed connections, for instance : listen_addresses = '192.168.1.200, 192.168.1.201' or listen_addresses = '*' (to listen on all of the server IP addresses)

Code: Postgresql.conf
 
 #--------------------------------------------------------------------
 # CONNECTIONS AND AUTHENTICATION
 #--------------------------------------------------------------------
 # - Connection Settings -
 listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
 #port = 5432 
 max_connections = 100
 # note: increasing max_connections costs ~400 bytes of shared memory per
 # connection slot, plus lock space (see max_locks_per_transaction).  You
 # might also need to raise shared_buffers to support more connections.
 #superuser_reserved_connections = 2
 #unix_socket_directory = ''
 #unix_socket_group = ''
 #unix_socket_permissions = 0777         # octal
 #bonjour_name = ''                      # defaults to the computer name
 
 # - Security & Authentication -
 
 #authentication_timeout = 60            # 1-600, in seconds
 ssl = on
 #password_encryption = on
 #db_user_namespace = off
  

If you want to connect over a network or worse the internet. Consider encrypting your data and connection with SSL and/or Kerberos. The options are found in the same section. Be sure to also read the next section on Setting Internal Connection to set authentication method for the network connections.

Setting Internal Connections

The default settings will allow ANY local connection for as long as the username checks out. I find it more secure if all users also must provide a password in order to get their database connection. I think most of us would agree on that !

Open the pg_hba.conf file

# nano -w /var/lib/postgresql/data/pg_hba.conf

Make it look like this

Code: pg_hba.conf
 
 # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 # "local" is for Unix domain socket connections only
 local   all         postgres                          trust
 local   all         all                               md5
 # IPv4 local connections:
 host    all         all         127.0.0.1/32          md5
 # IPv6 local connections:
 host    all         all         ::1/128               md5
 

This will ensure that all local users must provide a valid password and the password is encrypted by md5 (kerberos encryption is more secure ... but requires additional settings and 'kerberos' to be set in your USE variable). The only exception is 'postgres' user since it needs this in order to create database users and databases for you. This of course dictates to you as database administrator that ALL database users MUST have a password (which is always a good idea). But if you follow this wiki that won't be a problem. Then for external connection add the following line (provided they are ssl encrypted)

Code: pg_hba.conf
 
  hostssl all         all         *           md5
  

NOTE: For more recent versions of postgres (for example, 8.0.15) the * gives an error. Use this instead:

Code: pg_hba.conf
 
  hostssl all         all         0.0.0.0/0           md5
  

Logging

By default the logging is off. This is not recommended since logs are the only way to properly determine what went wrong in cases of emergency (crashes, slow performance etc). Even if you or your own (written) programs are the only users the postgresql server it is still recommended you set some kind of logging. How much to log is up to you a just keep in mind: The more you log the more likely you are to find your problem but the more time it will take to find it. Here is my default logging configuration which should suffice for most users.

In order for postgres to log to the '/var/log' directory it must first have access to it. Therefore we make a directory and make postgres its owner. You can skip this if you decided to log into (a sub directory) of your PGDATA directory.

# mkdir /var/log/pglog
# chown postgres:postgres -R /var/log/pglog/

Open the postgresql.conf file

 # nano -w /var/lib/postgresql/data/postgresql.conf

Then make the following changes check also below for full details

NOTE: As of postgresql 8.0.15 instead of on/off there are true/false switches in configuration. I think it's also the case for every 8.0.x, so beware.

Code: postgresql.conf
 
 #---------------------------------------------------------------------------
 # ERROR REPORTING AND LOGGING
 #---------------------------------------------------------------------------

 # - Where to Log -

 #log_destination = 'stderr'            

 # This is used when logging to stderr:
 redirect_stderr = on                    

 # These are only used if redirect_stderr is on:
 log_directory = '/var/log/pglog'       

 log_filename = 'pgsql-%Y-%m-%d.log'

 #log_truncate_on_rotation = off         

 #log_rotation_age = 1440     

 #log_rotation_size = 10240    


 # These are relevant when logging to syslog:
 #syslog_facility = 'LOCAL0'
 #syslog_ident = 'postgres'


 # - When to Log -

 #client_min_messages = notice      

 #log_min_messages = notice             

 #log_error_verbosity = default

 log_min_error_statement = error

 #log_min_duration_statement = -1

 #silent_mode = off 

 # - What to Log -

 #debug_print_parse = off
 #debug_print_rewritten = off
 #debug_print_plan = off
 #debug_pretty_print = off
 log_connections = on
 #log_disconnections = off
 #log_duration = off
 log_line_prefix = '<%t> '

 log_statement = 'ddl'
 #log_hostname = off

Log Rotation

After you've decided to configure some kind of logging for Postgresql, the next step is to prevent accumulating log data from taking over all your hard disk space. Postgresql will rotate the log file but doesn't delete obsolete logs (rotate 6 weeks old logs out of existence). When you installed Gentoo you (should) have emerged logrotation to handle your syslog files. I will use this tool to rotate the logfiles of postgresql. You can also make a bash script and load it into your cron daemon however I found it cleaner to keep logrotation in one place and better for maintenance. In case you didn't install logrotation for your syslog, here is how to do it:

 emerge logrotate

Main configuration file is /etc/logrotate.conf and additional logrotate files are placed in /etc/logrotate.d/. Then it is time to make our logrotation entry

 nano -w /etc/logrotate.d/postgresql
Code: postgresql
 
# Compress postgresql log files
# Provided by DouweQuerty
/var/log/pglog/pglog.tar {
  missingok
  ifempty
  rotate 7
  weekly
  sharedscripts
  prerotate
    cd /var/log/pglog/
    tar -C /var/log/pglog/ -cp --remove-files --wildcards -f pglog.tar --exclude *$(date +%Y-%m-%d)* *.log
  endscript
  postrotate
  endscript
}

This will collect (tar) all the existing logfiles, except today's logfile, before rotating them. Because the active logfile isn't touched, no reload of postgresql is needed. But we do need to create a dummy tar file else logrotation doesn't do its job.

 echo "" > /var/log/pglog/pglog.tar

Then check whether it is working by forcing a logrotation:

 logrotate -f /etc/logrotate.conf

Install a Procedural Language

You can install procedural languages, such as postgresql's pl/pgsql, into a template. Using the newly created template will make the procedural languages available to all databases created using that template. This is purely optional and if you don't know or want to know what templates are please skip this section. You can read this section in the future, should the need for procedural languages arise. For those who are interested, here we go: First create a template called 'template1' and include the postgresql's pl/pgsql

# su postgres
# createlang plpgsql template1

Since 'template1' will be automatically added to any new database which will be created we are all done here! If you decided to skip ahead and now want to add the 'pl/pgsql' language to an existing database here is how:

# su postgres
# createlang plpgsql MY_DATABASE

Make sure you replace MY_DATABASE with your own database's name.

Installing JDBC driver

JDBC is the intermediate between Java and postgresql. Thus allowing Java application to execute SQL statements and retrieve information from the database. Since the JDBC driver are written in Java itself there is a significant speed advantage over the JDBC-ODBC bridge. Installing it is very easy :

# emerge -av jdbc-postgresql

Make sure you have the latest version of java installed on your system for these drivers to work. I also must point out that the new jdbc4 drivers are coming. At this moment they are released by postgres and will in time be part of the Gentoo.

Finish and Start

After you have configured postgresql it is time to fire it up :

#/etc/init.d/postgresql start

The following command will ensure that postgresql is loaded at boot

# rc-update add postgresql default

Time for a quick test of your newly installed postgresql server. Thus

# psql -U postgres

Then enter the following command

select current_date;

The result should look like this :

Code: Testing postgresql
 
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# select current_date;
    date
------------
 2007-04-02
(1 row)

postgres=# \q


Upgrading Postgresql

First a warning : This was taken from an page where there was some discussion whether this should work or not. If you are smart you backup your postgresql before trying this (which is always a sensible thing to do when you are upgrading). I have added this section with remarks and comments thus if you know something more or worked out how this works please add it or contribute to the discussion part of this page.

When upgrading emerge often says

 * Postgres n.n.n cannot upgrade your existing databases, you must
 * use pg_dump to export your existing databases to a file, and then
 * pg_restore to import them when you have upgraded completely.

This has caused me some grief in the past and I'm clearly not the only one so here's my attempt at documenting how to do it. The key thing is that emerge speaks with forked tongue, it's not pg_restore that you need, but more on this below.

As mentioned, you first need to backup your databases. There are two ways:

  1. If you have multiple databases, it is easier to use the pg_dumpall program. This causes all data in the database to be dumped to the a single file. The syntax is:
     pg_dumpall -U sqluser > /backupplace/backup.sql 
    Because pg_dumpall needs to connect to each database separately, it will ask you for the user password each time it tries to connect to a new database. So configure an account to use trust or ident authentication in pg_hba.conf before running pg_dumpall if you want to avoid this. Or, use the "postgres" user. Note. If you have chanced pg_hba.conf then you need to back that up.
  2. If you have only one database, it is also possible to backup only this one (but you will loose your self created internal postgresql users ...not if you back them up using pg_dump -U sqlusr -s database1 > /backupplace/users.sql). You need to perform a command something like this:
     pg_dump -C -U sqlusr database1 > /backupplace/backup1.sql 
    You'll need to repeat the above for each database you've got using different output filenames each time.

    You can also do
    pg_dumpall --globals-only > globals.pgsql
    when dumping individual database with pg_dump. That way, you'll have the separate databases, and the globals (like users).

Having backed up your database(s) to files, stop postgresql:

/etc/init.d/postgresql stop

You then need to "remove" the database directory. You'll remember that emerge says:

 * You must remove your entire database directory to continue.
 * (database directory = /var/lib/postgresql).

If you're paranoid like me then rather than remove it what you'll do is move it to one side thus:

cd /var/lib
mv postgresql postgresql.old

Now let emerge update postgresql. Once that's done re-install it using the emerge --config command used above. Be sure to set an appropriate LANG environment before issuing this command. An unset LANG will lead to an ASCII-encoded database, while for example LANG=de_DE sets it up using latin1 encoding.


After that, you should set up your config files by copying your pg_hba.conf and/or (portions of your) postgresql.conf file from your backup to the new database location. Then fire postgresql up so you can start populating the database with your data again.

/etc/init.d/postgresql start


  1. If you used the pg_dumpall method, we can simply use the following command to restore your entire database (including self-created users):
     psql template1 < /backupplace/backup.sql 
    We do this from the command line as user postgres, so make sure that backup.sql is readable by this user.
  2. Remember to create the user(s) (in my case sql-ledger) and each database (in my case the-hug). Then use psql (yup, not pg_restore) to restore the database(s) thus:
     psql -U sqluser template1 < /backupplace/database1.sql 


If you have large databases, it is possible to use some command-line options of pg_dumpall to output the sql code to an archive. You will need to use pg_restore to restore your database then though.


A note from the Postgresql manual: Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics. You can also run vacuumdb -a -z to analyze all databases.


Remember that if this fails you can always use emerge to fall back to the old release and then restore your database which, thanks to your foresight, still lurks in /var/lib/postgresql.old


See the postgres upgrade FAQ entry for more info.

Troubleshooting

In the past (postgresql 7.x.x) the following message could show itself. It shouldn't display itself anymore but just in case it does here is how to fix it :

su: Authentication service cannot retrieve authentication info.

This indicates that su cannot find an entry for the user posgres in /etc/shadow. This can be solved by just adding one:

pwconv

If you get errors like "psql: FATAL: user "root" does not exist" when running commands such as

#  psql -l

then remember to run them as the postgres user

#  su - postgres
Retrieved from "http://www.gentoo-wiki.info/PostgreSQL/Install"

Last modified: Fri, 29 Aug 2008 14:57:00 +1000 Hits: 22,112

Created by NickStallman.net, Luxury Homes Australia
Real estate agents should list their apartments, townhouses and units in Australia.