Posgres Server Configuration
See also:
Note that the server is now on 128.32.204.203 (having been moved to the new colo)
Contents
Mount Bear
Mounting Bear makes data transfer for the build easier...
mkdir /mnt/ed mount -t cifs //bear/ed_egan/ /mnt/ed -o user=haas\\ed_egan
Check the spec
Run some basic commands to check the spec of the box
uname -a Linux PhD-postgres2 2.6.18-274.12.1.el5 #1 SMP Tue Nov 29 13:37:46 EST 2011 x86_64 x86_64 x86_64 GNU/Linux cat /etc/issue CentOS release 5.7 (Final) cat /proc/version Linux version 2.6.18-274.12.1.el5 gmake --version GNU Make 3.81 (need >3.8) perl -V check for: usemultiplicity=define python -v (ctrl-D) to get out if it works
Build Postgres
Download a copy of Postgres 9.1.2 and put it in /home/ed/ (not on the mount - have it local) Then:
gunzip postgresql-9.1.2.tar.gz tar xf postgresql-9.1.2.tar cd postgresql-9.1.2
Update missing packages needed for the build
yum install gcc gcc-c++ autoconf automake yum install readline-devel zlib-devel python-devel
Now do the actual install (Official Instructions):
./configure --with-perl --with-python --with-segsize=16 --with-blocksize=32 gmake All of PostgreSQL is successfully made. Ready to install. gmake world PostgreSQL, contrib and HTML documentation successfully made. Ready to install.
As root edit /etc/profile to include (before 'export PATH'):
PATH=/usr/local/pgsql/bin:$PATH
Configure the server
Add the postgres user and get her running:
adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 & /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test
Make a database user:
CREATE USER ed_egan WITH PASSWORD 'whatever';
Edit /etc/sysconfig/iptables to include:
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 128.32.66.0/24 --dport 5432 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 128.32.67.0/24 --dport 5432 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 128.32.74.0/24 --dport 5432 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 10.136.0.0/23 --dport 5432 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 136.152.208.0/22 --dport 5432 -j ACCEPT
/etc/init.d/iptables restart
Change postgres.conf:
listen_addresses = '*' port = 5432 max_connections = 100 shared_buffers = 4GB work_mem = 512MB maintenance_work_mem = 512MB effective_cache_size = 14GB
Add access permissions to pg_hba.conf
host all all 128.32.74.0/24 trust host all all 128.32.66.0/24 trust host all all 128.32.67.0/24 trust host all all 10.136.0.0/23 trust
Add to /etc/rc.local
su -c '/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data -l >/usr/local/pgsql/data/serverlog' postgres
Start postgres with one the following commands as the postgres user (if you've fixed the path then abbrev the first part)
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data (runs the server with output on the terminal) /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >/usr/local/pgsql/data/logfile 2>&1 & (runs the server with output in the logfile - note the path is needed for the logfile) /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/logfile start & (same as above but using the pg_ctl wrapper)
Check postgres is listening on 5432:
netstat -tulpn
Test
Test by connecting remotely using a psql client on your desktop. Then enjoy!
psql -h 128.32.252.201 -U ed_egan test
Adding Users
Adding Root Accounts to the box
Assuming that you have root, you can create user accounts on the box and give them root too. This isn't necessary for regular users - they just need a Postgres user account (see below). To add users to the box, the process is:
First great the users group, checking the last group number (5xx is the next one):
cat /etc/group /usr/sbin/groupadd -g 5xx username
Then add the user (it doesn't matter what you put for -p, it is going to be overwritten):
/usr/sbin/useradd -g username -G root -s /bin/bash -p xxxx -d /home/username -m username
where g is the primary group, G is other groups, p sets a password,
d declares a home directory and m makes the directory
Change the user's password:
passwd username
And add the user to the sudoers file
echo 'username ALL=(ALL) ALL' >> /etc/sudoers
Deleting a user
To delete a user:
/usr/sbin/userdel -r roger where r removes the home directory
And to remove their group
/usr/sbin/groupdel username
And remove their entry from the sudoers file too if they had root.
Adding Postgres Accounts
Log on to the box as root then:
su postgres
To work as the postgres account.
Now add the user using:
/usr/local/pgsql/bin/createuser username (Answer y or n to whether you want the new role to be superuser - generally y)
If the user doesn't have an account on the box, then you'll need to add a database for them (use DBname of 'firstname_data' as a default):
/usr/local/pgsql/bin/createdb -O username DBname
Size, Backup & Restore
Find the sizes of databases on our postgres server
- Connect to the dbase server (poss. with an admin account)
- psql postgres
- run the following query:
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20;
To see the disk space use on all drives use:
df -h
Backing up a dbase
pg_dump dbase > dbase.dump or pg_dump -Fc dbase > dbase_fc.dump (which uses compression)
Note: Use top or ps -aux to see memory/CPU usage.
We can also manually compress/decompress using:
gzip filename gzip -d filename.gz
When done, we can drop the database:
dropdb dbase
Restoring a dbase
pg_restore -d newdb dbase.dump
