Posted by: jasonk2600 | January 11, 2010

Installing PostgreSQL on FreeBSD

Synopsis

This post will describe the steps required to complete the initial configuration of the PostgreSQL DBMS on a system running FreeBSD.  Configuring the basic configuration of PostgreSQL and creating an initial super-user will be covered; performance tuning, database administration, and Structured Query Language (SQL) will not be covered in this post.

Installation

Configure and install the PostgreSQL v8.4.x DBMS from the ports collection.  The default port configuration will work just fine, although you may consider enabling the OPTIMIZED_CFLAGS option for better performance.

# cd /usr/ports/databases/postgresql84-server
# make config
# make install clean

 

Configuration

Enable PostgreSQL to start at system boot in /etc/rc.conf.

# echo 'postgresql_enable="YES"' >> /etc/rc.conf

 

Initialize the PostgreSQL database cluster for the first time.  NOTE: The following command will create the initial database cluster in the /usr/local/pgsql/data directory by default.

# /usr/local/etc/rc.d/postgresql initdb
/usr/local/etc/rc.d/postgresql initdb
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale C.
The default text search configuration will be set to "english".

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 40
selecting default shared_buffers ... 28MB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/postgres -D /usr/local/pgsql/data
or
    /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

 

Configure PostgreSQL to listen for database connections on all system IP addresses by adding the following line to /usr/local/pgsql/data/postgresql.conf.

listen_addresses = '*'

 

Configure PostgreSQL to use password hash authentication for all hosts and users connecting from the local network by adding the following line to the /usr/local/pgsql/data/pg_hba.conf file. NOTE: Replace 10.0.1.0/24 with your own network.

host  all  all  10.0.1.0/24  md5

 

Start the PostgreSQL DBMS for the first time and add a new super-user (with database and role creation rights) by executing the following commands.

# /usr/local/etc/rc.d/postgresql start
# su pgsql
$ createuser -sdrP username
Enter password for new role: ******
Enter it again: ******

$ exit

 

Testing

The PostgreSQL DBMS should now be up and running with the newly created super-user. Using a PostgreSQL client, such as pgadmin3 connect to the PostgreSQL server from another system using the username and password of the role previously created. New database schemas, roles, procedures, etc can now be created using the super-user.

References

 

  [EoF]
About these ads

Responses

  1. [...] Installing PostgreSQL on FreeBSD. [...]

  2. Thank you for the walk-through; you probably just saved me a day’s worth of computer ‘fun’.

  3. Thanks for this great tutorial. saved me a lot of trouble

    kind regards

    erik

  4. Ditto on the thanks. It always shocks me how this type of stuff is missing from the sources you most expect it from.

    Thanks.

  5. Jason thanx for all. it was perfect.
    But still i hate freebsd :)

  6. This worked fine.

  7. Thank you for great article!
    It helped me a lot.
    It is veary easy to understand. You should start writing official documentation ;)

  8. Muchísimas gracias! Esta guía me funcionó genial para instalar y configurar PostgreSQL en FreeBSD 8.2-RELEASE. CONGRATULATIONS!


Leave a Reply

Fill in your details below or click an icon to log in:

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

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: