This is running on a CentOS 6.2, which I created as a VM on a Citrix 6.0 XenCenter. Gave it 3Gb of RAM, as I have to import a 3Gb Database.
Step 1: Get Postgres installed.
The easiest way to do this is with yum, if you have redhat then configure your yum repositories or if you are registered with RHN, just do:
I did that, this is the output I get when I did it again (obviously, but Im just showing you the results)
root@POSTGRES-XEN [ ~ ]# yum install postgresql
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: centos.omnispring.com
* extras: centos.omnispring.com
* updates: mirror.lug.udel.edu
Setting up Install Process
Package postgresql-8.4.9-1.el6_1.1.x86_64 already installed and latest version
Nothing to do
root@POSTGRES-XEN [ ~ ]#
Now, we will su into the user created, postgres, and start the work:
This is what you will get:
root@POSTGRES-XEN [ ~ ]# su - postgres
postgres@POSTGRES-XEN [ ~ ]$ initdb -D ./data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".
fixing permissions on existing directory ./data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in ./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:
postgres -D ./data
or
pg_ctl -D ./data -l logfile start
Now, we need to start the database, in the way it shows above:
pg_ctl -D ./data -l logfile start
(pg_ctl is at /usr/bin/ )
Step 2, is let's create the database and the user accessing it:
as the user postgres, execute:
createdb --owner=confluence confluence
then, go into the postgres console by typing:
postgres@POSTGRES-XEN [ ~ ]$ psql
psql (8.4.9)
Type "help" for help.
postgres=#\l
(You can list the databases like this; just type forward slash and small l:)
\l
You can see the database we created, to see size information about it, you can type:
postgres=# SELECT pg_size_pretty(pg_database_size('confluence'));
This is not so applicable now, but I needed it to see the size of this 3Gb database.
We dont have a user yet, so let's create one:
postgres=# CREATE USER confluence WITH PASSWORD 'myPassword';
If you want to change password, you would do:
postgres=# ALTER USER confluence WITH PASSWORD 'NewPassword';
(this was a database for a confluence installation)
To Delete a database (watch out!!) you do this:
postgres=# DROP DATABASE confluence ;
You can also see all the options that the database is running with with:
postgres=# SHOW ALL;
To see current activity on the database, you can do this:
select * from pg_stat_activity where current_query not like '<%';
You will get something similiar to this:
Next, we will give privilidges on the database to this new user:
postgres=# GRANT ALL PRIVILEGES ON DATABASE confluence to confluence;
To see what permissions your users have do:
SELECT * FROM pg_user;
You will get something like this:
postgres=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
------------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 10 | t | t | t | ******** | |
confluence | 16384 | f | f | f | ******** | |
(2 rows)
Now we will export the data from the origin database, I do this with this script:
pg_dump \
--file=postgres_confluence_data.ORIGINAL.dump \
--format=custom \
--ignore-version \
--schema=public \
--schema=otherschema \
--no-owner \
--verbose \
--no-privileges \
confluence
This is done from the Linux command line, not the Postgres SQL prompt.
however, in my case, since I was working on a very low end box, I had to change some kernel parameters, and add some swap space:
sysctl -w kernel.shmall=4194304
echo "67108864" > /proc/sys/kernel/shmmax
(you can also put this in /etc/sysctl.conf permanently, not in the format though!)
Then I created a 8Gb swap file (Box only had 1Gb vRAM)
root@POSTGRES-XEN [ ~ ]# dd if=/dev/zero of=/swapfilenew bs=1024 count=8388608
root@POSTGRES-XEN [ ~ ]# mkswap /swapfilenew
root@POSTGRES-XEN [ ~ ]# chown root:root /swapfilenew
root@POSTGRES-XEN [ ~ ]# chmod 0600 /swapfilenew
root@POSTGRES-XEN [ ~ ]# swapon /swapfilenew
When I have this file, I then restore it to the new database like this:
pg_restore \
--format=custom \
--ignore-version \
--no-owner \
--no-privileges \
--verbose \
-U confluence -d confluence \
~postgres/postgres_confluence_data.ORIGINAL.dump
After the database is comfortably sitting in the new location, we want to also make sure that
it can be accessed.
You have to edit your pg_hba.conf and go to the bottom and add your networks that you trust to connect to the database:
# IPv6 local connections:
host all all ::1/128 trust
host all all 12.18.96.0/24 trust
host all all 10.18.97.0/24 trust
host all all 10.13.235.0/24 trust
host all all 10.15.194.0/24 trust
Also, go to your postgresql.conf file and uncomment this line:
listen_addresses = '*'
Then go to your other server and telnet to port 5432 or run a query from there. Watch out if it's an out the box linux server, that iptables is not running, in my case I disabled it:
root@POSTGRES-XEN [ ~ ]# service iptables stop
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Unloading modules: [ OK ]
root@POSTGRES-XEN [ ~ ]#
root@POSTGRES-XEN [ ~ ]# chkconfig iptables off
root@POSTGRES-XEN [ ~ ]# chkconfig --list | grep iptables
iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off
root@POSTGRES-XEN [ ~ ]#
You can also check to see if the daemon is listening:
root@POSTGRES-XEN [ ~ ]# netstat -alnp |grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 2828/postgres
tcp 0 0 12.18.97.12:5432 10.13.235.8:45189 ESTABLISHED 10894/postgres
tcp 0 0 :::5432 :::* LISTEN 2828/postgres
unix 2 [ ACC ] STREAM LISTENING 22361 2828/postgres /tmp/.s.PGSQL.5432
root@POSTGRES-XEN [ ~ ]#
That's about it... I will hopefully do a follow up article on the confluence server as well.
