Index: openacs-4/packages/acs-core-docs/www/postgres.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/postgres.adp,v diff -u -r1.1.2.4 -r1.1.2.5 --- openacs-4/packages/acs-core-docs/www/postgres.adp 9 Jun 2016 13:03:11 -0000 1.1.2.4 +++ openacs-4/packages/acs-core-docs/www/postgres.adp 21 Jun 2016 07:44:36 -0000 1.1.2.5 @@ -14,504 +14,18 @@
OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff.Skip this section if you will run only Oracle.
OpenACS 5.9.0 will run with PostgreSQL -7.3.2, 7.3.3, and 7.3.4 and 7.4.x. 7.4.7 is the recommended version -of PostgreSQL.
-Special notes for Mac OS -X. If you are running Mac OS X prior to 10.3, you -should be able to install and use PostGreSQL 7.3.x. Mac OS X 10.3 -requires PostGreSQL 7.4.
Debian stable user should install PostGreSQL from source as -detailed below, or they should use the www.backports.org backport -for Postgres to get a more current version. Debian unstable users: -the following process has been known to work (but you should -double-check that the version of PostGreSQL is 7.3 or above):
For Debian stable users, you can use backports, by adding this -line to the /etc/apt/sources.list
deb http://www.backports.org/debian stable bison postgresql openssl openssh tcl8.4 courier debconf spamassassin tla diff patch neon chkrootkit
-
apt-get install postgresql postgresql-dev postgresql-doc
-ln -s /usr/include/postgresql/ /usr/include/pgsql
-ln -s /var/lib/postgres /usr/local/pgsql
-ln -s /usr/include/pgsql /usr/local/pgsql/include
-su postgres -c "/usr/lib/postgresql/bin/createlang plpgsql template1"
and proceed to Tune postgres. (OPTIONAL) or to the -next section.
--Special Notes for Red -Hat. Red Hat users: If you install PostgreSQL 7.3.2 -from the Red Hat 9 RPM, you can skip a few steps. These shell -commands add some links for compatibility with the directories from -a source-based install; start the service; create a new group for -web service users, and modify the postgres user's environment -(more -information):
-[root root]#ln -s /usr/lib/pgsql/ /var/lib/pgsql/lib
-[root root]#ln -s /var/lib/pgsql /usr/local/pgsql
-[root root]#ln -s /etc/init.d/postgresql /etc/init.d/postgres
-[root root]#ln -s /usr/bin /usr/local/pgsql/bin
-[root root]#service postgresql start
-Initializing database: - [ OK ] -Starting postgresql service: [ OK ] -[root root]#echo "export LD_LIBRARY_PATH=/usr/local/pgsql/lib" >> ~postgres/.bash_profile
-[root root]#echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bash_profile
-[root root]#groupadd web
-[root root]#su - postgres
--bash-2.05b$ - -ln -s /usr/lib/pgsql/ /var/lib/pgsql/lib -ln -s /var/lib/pgsql /usr/local/pgsql -ln -s /usr/bin /usr/local/pgsql/bin -service postgresql start -echo "export LD_LIBRARY_PATH=/usr/local/pgsql/lib" >> ~postgres/.bash_profile -echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bash_profile -groupadd web -su - postgres -
... and then skip to 8. Something similar may work -for other binary packages as well.
-Safe approach: install from source
-Unpack PostgreSQL 7.4.7. If you have not
-downloaded the postgresql tarball to /var/tmp/postgresql-7.4.7.tar.gz
, get
-it.
-[root root]#-cd /usr/local/src
-[root src]#tar xzf /var/tmp/postgresql-7.4.7.tar.gz
-[root src]# -cd /usr/local/src -tar xzf /var/tmp/postgresql-7.4.7.tar.gz -
-ALTERNATIVE: Unpack PostgreSQL 7.4.7. If you
-have not downloaded the postgresql tarball to /var/tmp/postgresql-7.4.7.tar.bz2
,
-get
-it.
-[root root]#-cd /usr/local/src
-[root src]#tar xfj /var/tmp/postgresql-7.4.7.tar.bz2
-[root src]# -cd /usr/local/src -tar xfj /var/tmp/postgresql-7.4.7.tar.bz2 -
-Install Bison. Only do this if bison --version
is smaller than
-1.875 and you install PostgreSQL 7.4 from cvs instead of
-tarball.
-[root root]#-cd /usr/local/src
-[root src]#wget http://ftp.gnu.org/gnu/bison/bison-1.875.tar.gz
-[root src]#tar xfz bison-1.875.tar.gz
-[root src]#cd bison-1.875
-[root src]#./configure
-[root src]#make install
-
-Create the Postgres user. Create a user and -group (if you haven't done so before) for PostgreSQL. This is the -account that PostgreSQL will run as since it will not run as root. -Since nobody will log in directly as that user, we'll leave the -password blank.
Debian users should probably use adduser instead of useradd.
-Type man adduser
-
-[root src]#groupadd web
-[root src]#useradd -g web -d /usr/local/pgsql postgres
-[root src]#mkdir -p /usr/local/pgsql
-[root src]#chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.4.7
-[root src]#chmod 750 /usr/local/pgsql
-[root src]# -groupadd web -useradd -g web -d /usr/local/pgsql postgres -mkdir -p /usr/local/pgsql -chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.4.7 -chmod 750 /usr/local/pgsql -
-Mac OS X: Do instead: First make sure the -gids and uids below are available (change them if they are not).To -list taken uids and gids:
nireport / /groups name gid | grep "[0123456789][0123456789]"
-nireport / /users name uid | grep "[0123456789][0123456789]"
Now you can install the users
sudo niutil -create / /groups/web
-sudo niutil -createprop / /groups/web gid 201
-sudo niutil -create / /users/postgres
-sudo niutil -createprop / /users/postgres gid 201
-sudo niutil -createprop / /users/postgres uid 502
-sudo niutil -createprop / /users/postgres home /usr/local/pgsql
-sudo niutil -create / /users/$OPENACS_SERVICE_NAME
-sudo niutil -createprop / /users/$OPENACS_SERVICE_NAME gid 201
-sudo niutil -createprop / /users/$OPENACS_SERVICE_NAME uid 201
-mkdir -p /usr/local/pgsql
-chown -R postgres:web /usr/local/pgsql /usr/local/src/postgresql-7.4.7
-chmod 750 /usr/local/pgsql
--FreeBSD users: need to add more -parameters.
-[root src]#-mkdir -p /usr/local/pgsql
-[root src]#pw groupadd -n web
-[root src]#pw useradd -n postgres -g web -d /usr/local/pgsql -s /bin/bash
-[root src]#chown -R postgres:web /usr/local/pgsql /usr/local/src/postgresql-7.4.7
-[root src]#chmod -R 750 /usr/local/pgsql
-[root src]# -mkdir -p /usr/local/pgsql -pw groupadd -n web -pw useradd -n postgres -g web -d /usr/local/pgsql -s /bin/bash -chown -R postgres:web /usr/local/pgsql /usr/local/src/postgresql-7.4.7 -chmod -R 750 /usr/local/pgsql -
-Set up postgres's environment variables. They -are necessary for the executable to find its supporting libraries. -Put the following lines into the postgres user's environment.
-[root src]#su - postgres
-[postgres ~]emacs ~postgres/.bashrc
-
Paste this line into .bash_profile
:
-source $HOME/.bashrc -
Paste these lines into .bashrc
:
-export PATH=/usr/local/bin/:$PATH:/usr/local/pgsql/bin -export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib -
Test this by logging in as postgres
and checking the paths; you should
-see /usr/local/pgsql/bin
-somewhere in the output (the total output is system-dependent so
-yours may vary)
-[root src]#su - postgres
-[postgres pgsql]$env | grep PATH
-LD_LIBRARY_PATH=:/usr/local/pgsql/lib -PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin:/usr/local/pgsql/bin:/usr/local/pgsql/bin -[postgres pgsql]$exit
-
Don't continue unless you see correct output from env | grep PATH
-
-Compile and install PostgreSQL. Change to
-the postgres user and run ./configure
to set the compilation options
-automatically. This is the point at which you can configure
-PostgreSQL in various ways. For example, if you are installing on
-"OS X" add the flags --with-includes=/sw/include/
---with-libraries=/sw/lib
. If you want to see what the other
-possibilities are, run ./configure
---help
.
On debian woody (stable, 3.0), do ./configure --without-readline
---without-zlib
.
-[root src]#-su - postgres
-[postgres pgsql]$cd /usr/local/src/postgresql-7.4.7
-[postgres postgresql-7.4.7]$./configure
-creating cache ./config.cache -checking host system type... i686-pc-linux-gnu -(many lines omitted> -linking ./src/makefiles/Makefile.linux to src/Makefile.port -linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s -[postgres postgresql-7.4.7]$make all
-make -C doc all -make[1]: Entering directory `/usr/local/src/postgresql-7.4.7/doc' -(many lines omitted) -make[1]: Leaving directory `/usr/local/src/postgresql-7.4.7/src' -All of PostgreSQL successfully made. Ready to install. -[postgres postgresql-7.4.7]$make install
-make -C doc install -make[1]: Entering directory `/usr/local/src/postgresql-7.4.7/doc' -(many lines omitted) -Thank you for choosing PostgreSQL, the most advanced open source database -engine. -su - postgres -cd /usr/local/src/postgresql-7.4.7 -./configure -make all -make install -
-Start PostgreSQL. The initdb
command initializes the database.
-pg_ctl
is used to start up
-PostgreSQL. If PostgreSQL is unable to allocate enough memory, see
-section 11 Tuning PostgreSQL (below).
-[postgres postgresql-7.4.7]$/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
-The files belonging to this database system will be owned by user "postgres". -This user must also own the server process. -(17 lines omitted) -or - /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start -[postgres postgresql-7.4.7]$/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start
-postmaster successfully started -[postgres postgresql-7.4.7]$ -/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start -
PostgreSQL errors will be logged in /usr/local/pgsql/data/server.log
-
-Install Pl/pgSQL. Set up plpgsq and allow -your user to have access. Plpgsql is a PL/SQL-like language. We add -it to template1, which is the template from which all new databases -are created. We can verify that it was created with the createlang -command in list mode.
-[postgres postgresql-7.4.7]$-createlang plpgsql template1
-[postgres pgsql]$createlang -l template1
-Procedural languages - Name | Trusted? ----------+---------- - plpgsql | t -(1 row) - -[postgres pgsql-7.4.7]$ -createlang plpgsql template1 -createlang -l template1 -
-Test PostgreSQL (OPTIONAL). Create a database -and try some simple commands. The output should be as shown.
-[postgres pgsql]$-createdb mytestdb
-CREATE DATABASE -[postgres pgsql]$psql mytestdb
-Welcome to psql, the PostgreSQL interactive terminal. - -Type: \copyright for distribution terms - \h for help with SQL commands - \? for help on internal slash commands - \g or terminate with semicolon to execute query - \q to quit - -mytestdb=#select current_timestamp;
- timestamptz -------------------------------- - 2003-03-07 22:18:29.185413-08 -(1 row) - -mytestdb=#create function test1() returns integer as 'begin return 1; end;' language 'plpgsql';
-CREATE -mytestdb=#select test1();
- test1 -------- - 1 -(1 row) - -mytestdb=#\q
-[postgres pgsql]$dropdb mytestdb
-DROP DATABASE -[postgres pgsql]$exit
-logout - -[root src]# -
-Set PostgreSQL to start on boot. -First, we copy the postgresql.txt init script, which automates -startup and shutdown, to the distribution-specific init.d -directory. Then we verify that it works. Then we automate it by -setting up a bunch of symlinks that ensure that, when the operating -system changes runlevels, postgresql goes to the appropriate state. -Red Hat and Debian and SuSE each work a little differently. If you -haven't untarred the -OpenACS tarball, you will need to do so now to access the -postgresql.txt file.
Red Hat RPM:
The init script is already installed; just turn it on for the -appropriate run levels.
-[root root]# chkconfig --level 345 postgresql on
-[root root]#
-
-Red Hat from source:
-[root src]#cp /var/tmp/openacs-5.9.0/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
-[root src]#chown root.root /etc/rc.d/init.d/postgresql
-[root src]#chmod 755 /etc/rc.d/init.d/postgresql
-[root src]# -cp /var/tmp/openacs-5.9.0/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql -chown root.root /etc/rc.d/init.d/postgresql -chmod 755 /etc/rc.d/init.d/postgresql -
Test the script.
-[root root]# service postgresql stop
-Stopping PostgreSQL: ok
-[root root]#
-
If PostgreSQL successfully stopped, then use the following -command to make sure that the script is run appropriately at boot -and shutdown. And turn it back on because we'll use it later.
-[root root]#-chkconfig --add postgresql
-[root root]#chkconfig --level 345 postgresql on
-[root root]#chkconfig --list postgresql
-postgresql 0:off 1:off 2:on 3:on 4:on 5:on 6:off -[root root]#service postgresql start
-Starting PostgreSQL: ok -[root root]# -chkconfig --add postgresql -chkconfig --level 345 postgresql on -chkconfig --list postgresql -service postgresql start -
Debian:
-[root ~]#cp /var/tmp/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
-[root ~]#chown root.root /etc/init.d/postgresql
-[root ~]#chmod 755 /etc/init.d/postgresql
-[root ~]# -cp /var/tmp/openacs-5.9.0/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql -chown root.root /etc/init.d/postgresql -chmod 755 /etc/init.d/postgresql -
Test the script
-[root ~]# /etc/init.d/postgresql stop
-Stopping PostgreSQL: ok
-[root ~]#
-
If PostgreSQL successfully stopped, then use the following -command to make sure that the script is run appropriately at boot -and shutdown.
-[root ~]#-update-rc.d postgresql defaults
- Adding system startup for /etc/init.d/postgresql ... - /etc/rc0.d/K20postgresql -> ../init.d/postgresql - /etc/rc1.d/K20postgresql -> ../init.d/postgresql - /etc/rc6.d/K20postgresql -> ../init.d/postgresql - /etc/rc2.d/S20postgresql -> ../init.d/postgresql - /etc/rc3.d/S20postgresql -> ../init.d/postgresql - /etc/rc4.d/S20postgresql -> ../init.d/postgresql - /etc/rc5.d/S20postgresql -> ../init.d/postgresql -[root ~]#/etc/init.d/postgresql start
-Starting PostgreSQL: ok -[root ~]# -
FreeBSD:
-[root ~]#cp /tmp/openacs-5.9.0/packages/acs-core-docs/www/files/postgresql.txt /usr/local/etc/rc.d/postgresql.sh
-[root ~]#chown root:wheel /usr/local/etc/rc.d/postgresql.sh
-[root ~]#chmod 755 /usr/local/etc/rc.d/postgresql.sh
-[root ~]# -cp /tmp/openacs-5.9.0/packages/acs-core-docs/www/files/postgresql.txt /usr/local/etc/rc.d/postgresql.sh -chown root:wheel /usr/local/etc/rc.d/postgresql.sh -chmod 755 /usr/local/etc/rc.d/postgresql.sh -
Test the script
-[root ~]# /usr/local/etc/rc.d/postgresql.sh stop
-Stopping PostgreSQL: ok
-[root ~]#
-
If PostgreSQL successfully stopped, then turn it back on because -we'll use it later.
-[root root]# /usr/local/etc/rc.d/postgresql.sh start
-Starting PostgreSQL: ok
-[root root]#
-/usr/local/etc/rc.d/postgresql.sh start
-
-SuSE:
I have received reports that SuSE 8.0 is different from previous
-versions. Instead of installing the boot scripts in /etc/rc.d/init.d/
, they should be placed in
-/etc/init.d/
. If you're using
-SuSE 8.0, delete the rc.d/
part
-in each of the following commands.
-[root ~]#cp /var/tmp/openacs-5.9.0/packages/acs-core-docs/www/files/postgresql.txt /etc/rc.d/init.d/postgresql
-[root ~]#chown root.root /etc/rc.d/init.d/postgresql
-[root ~]#chmod 755 /etc/rc.d/init.d/postgresql
-
Test the script.
-[root ~]# /etc/rc.d/init.d/postgresql stop
-Stopping PostgreSQL: ok
-
If PostgreSQL successfully stopped, then use the following -command to make sure that the script is run appropriately at boot -and shutdown.
-[root ~]#cd /etc/rc.d/init.d
-root:/etc/rc.d/init.d#ln -s /etc/rc.d/init.d/postgresql K20postgresql
-root:/etc/rc.d/init.d#ln -s /etc/rc.d/init.d/postgresql S20postgresql
-root:/etc/rc.d/init.d#cp K20postgresql rc2.d
-root:/etc/rc.d/init.d#cp S20postgresql rc2.d
-root:/etc/rc.d/init.d#cp K20postgresql rc3.d
-root:/etc/rc.d/init.d#cp S20postgresql rc3.d
-root:/etc/rc.d/init.d#cp K20postgresql rc4.d
-root:/etc/rc.d/init.d#cp S20postgresql rc4.d
-root:/etc/rc.d/init.d#cp K20postgresql rc5.d
-root:/etc/rc.d/init.d#cp S20postgresql rc5.d
-root:/etc/rc.d/init.d#rm K20postgresql
-root:/etc/rc.d/init.d#rm S20postgresql
-root:/etc/rc.d/init.d# -
Test configuration.
-root:/etc/rc.d/init.d #-cd
-root:~ #/etc/rc.d/init.d/rc2.d/S20postgresql start
-Starting PostgreSQL: ok -root:~ # -
Mac OS X 10.3:
Install the startup script:
--cd /System/Library/StartupItems/
tar xfz /var/lib/aolserver/$OPENACS_SERVICE_NAME/packages/acs-core-docs/www/files/osx-postgres-startup-item.tgz
-
Mac OS X 10.4 can use Launchd:
Install the startup script:
-cd /Library/LaunchDaemons
cp -/var/lib/aolserver/$OPENACS_SERVICE_NAME/packages/acs-core-docs/www/files/osx-postgres-launchd-item.txt -org.postgresql.PostgreSQL.plist
-
If postgres does not start automatically on reboot, see what -error you get when manually starting it with:
-$-sudo launchctl load /Library/LaunchDaemons/org.postgresql.PostgreSQL.plist
-$sudo launchctl start org.postgresql.PostgreSQL
-
From now on, PostgreSQL should start automatically each time you -boot up and it should shutdown gracefully each time you shut down. -(Note: Debian defaults to starting all services on runlevels 2-5. -Red Hat defaults to starting services on 3-5. So, on Red Hat, -PostgreSQL won't start on runlevel 2 unless you alter the above -commands a little. This usually isn't a problem as Red Hat defaults -to runlevel 3)
--Tune postgres. -(OPTIONAL). The default values for PostgreSQL are -very conservative; we can safely change some of them and improve -performance.
Change the kernel parameter for maximum shared memory segment -size to 128Mb:
-[root root]# echo 134217728 >/proc/sys/kernel/shmmax
-[root root]#
-
Make that change permanent by editing /etc/sysctl.conf
to add these lines at the
-end:
-# increase shared memory limit for postgres -kernel.shmmax = 134217728 --
Edit the PostgreSQL config file, /usr/local/pgsql/data/postgresql.conf
, to
-use more memory. These values should improve performance in most
-cases. (more information)
-# Shared Memory Size -# -shared_buffers = 15200 # 2*max_connections, min 16 - -# Non-shared Memory Sizes -# -sort_mem = 32168 # min 32 - - -# Write-ahead log (WAL) -# -checkpoint_segments = 3 # in logfile segments (16MB each), min 1 -
Restart postgres (service
-postgresql restart
) or (/etc/init.d/postgres restart
) so that the
-changes take effect.
FreeBSD users: See man
-syctl
, man 5
-sysctl
and man 5
-loader.conf
.
Performance tuning resources:
-Managing Kernel Resources about PostgreSQL shared -memory and semaphores with specific operating system notes.
-Managing Kernel Resources (development version) -This information may be experimental.
It is recommend to use a prepackaged version of PostgreSQL, +which are available in source and binary formats from www.postgresql.org/download/.
Larger installations might want to tune the PostgresSQL
+installation with e.g. the utility pgtune,
+which is also available via apt-get
+install pgtune
or dnf install pgtune
on
+Debian/Ubuntu or RedHat systems.