Screaming Electron Forums

Go Back   Screaming Electron Forums > How-To's & Testimonials > How-To's
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old July 11th, 2009
Kernel_Killer's Avatar
Kernel_Killer Kernel_Killer is offline
Administrator
 
Join Date: Dec 2002
Location: Misery
Posts: 1,334
Kernel_Killer will become famous soon enoughKernel_Killer will become famous soon enough
Default FreeBSD PostGRESQL 8.3 in Jails

Originally written by VileSYN (Kernel_Killer) of Network Synapse. Original Article

Since the release of FreeBSD 7.0 many things have been changed to allow PostGres to run better than any other database under FreeBSD. To take advantage of this, I'll show how to setup PostGreSQL in FreeBSD jails. Of course, the same method is used to install on your main system as well.



First off, we need to compile our kernel with a few different options.

Code:
# Shared Memory
options         SHMMAXPGS=400000  # Maximum size of shared memory segment (in pages)
options         SHMSEC=512   # Maximum number of shared memory segments per process
options         SEMMNI=512    # Maximum number of semaphore identifiers
options         SEMMNS=1024    # Maximum number of semaphores system-wide
options         SEMUME=200    # Maximum number of undo entries per process
options         SEMMNU=512    # number of undo structures in system
You may notice later that after installing PostGreSQL, it will mention the same thing, but with lesser amounts. Using the amounts given by the port won't allow as many connections, and is optimized for a system with roughly around 256M of memory. The system I'm building this how-to around has over 2 gigabytes of RAM, and allocating 1.6 gigabytes for PostGreSQL in shared memory. If you are using less, you might want to divide SHMMAXPGS accordingly. The others, if you are using one gigabyte total, you might cut those in half. Keep in mind, this setup is fairly light on the server, considering it's not meant to be a full-fledged PostGreSQL server.

For use of shared memory for jails, be sure to add this to your /etc/sysctl.conf:

Code:
security.jail.sysvipc_allowed=1
Compile your new kernel, reboot, and then on to ports. If you want to check your allocated shared memory, you can check with:

sysctl -a | grep shmmax

The settings above will show 'kern.ipc.shmmax: 1638400000'.

If you are planning to compile the database in a jail, be sure to mount the nullfs ports to the ports directory in the jail (refer to the FreeBSD 7.0 Jail How-To in order to see how this is done, if you have forgotten). Next, go to /usr/ports/databases/postgresql83-server, and do the usual, 'make install clean'. After it is done installing, edit your /etc/rc.conf, and add:

postgresql_enable="YES"

If you don't add this, the rc.d script will NOT work.

Next, you need to initialize the database. To do so, simply run:

/usr/local/etc/rc.d/postgresql initdb

With this done, it's time to change the setup of the database. First, we go to /usr/local/pgsql/data, and edit the postgresql.conf. In here, we'll set the bind-address, port, and whatever else we need. If you plan to have other jails, or the base system access the database, set the bind-address to the IP of the jail itself.

Next we move to the pg_hba.conf file. Here we set what hosts, and users we want to allow access to the database.

To start, we'll add some sort of remote access, say, via VPN (10.211.0.0/16).

Code:
host all all 10.211.0.0/16  password md5

With that in place, we restart the server so that the rules are initialized. Now to create a user.

First we log into the pgsql account.

Code:
$ su pgsql
$ createuser 
Enter name of role to add: testing
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n


Now, let's use the new user, create a table, and view it.

Code:
$ psql -d template1 -U testing
template1=> create database testing;
CREATE DATBASE
template1=> \l
        List of databases
   Name    |  Owner  | Encoding  
-----------+---------+-----------
 postgres  | pgsql   | SQL_ASCII
 template0 | pgsql   | SQL_ASCII
 template1 | pgsql   | SQL_ASCII
 testing   | testing | SQL_ASCII
(4 rows)


template1=> \c testing;
You are now connected to database "testing".
testing=> create table public.test_table ( id char(2), name varchar(40));
CREATE TABLE
testing=> \d
           List of relations
 Schema |    Name    | Type  |  Owner  
--------+------------+-------+---------
 public | test_table | table | testing
(1 row)

testing=>


Now, for future reference, let's see how to see users, and change the password of users. The database template1 is the main default database for PostGreSQL, and holds the user configurations.

Code:
testing=> \c template1 
You are now connected to database "template1".
template1=> alter user testing with password 'testme';
ALTER ROLE

Since you added a role in the pg_hba.conf file, you can connect remotely if needed with the following syntax:

Code:
 psql -h <hostname> -U <user> -d <database>

So, that's it for this tutorial. You should now have a basic understanding of PostGreSQL, and be able to install it with full optimization in FreeBSD, and even in a jail if needed. Enjoy!

Copyright Network Synapse
Reply With Quote
 


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
FreeBSD 7.0 and PostGreSQL 8.3 in Jails Kernel_Killer How-To's 2 May 16th, 2008 18:02
Building a Fairly secure FreeBSD Mail server soup4you2 How-To's 55 March 4th, 2006 04:34
[FreeBSD-Announce] FreeBSD Status Report July-December 2004 molotov News and Articles 0 January 18th, 2005 03:51
[FreeBSD-Announce] FreeBSD Foundation Quarterly Newsletter molotov News and Articles 0 December 27th, 2004 05:34


All times are GMT -4. The time now is 04:09.


Powered by vBulletin® Version 3.7.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
This Page hosted by Strog.org