PostgreSql is an Open Source project with a BSD license. It is a mature and stable database with transactions, stored procedures, and rollback. PostgreSQL is available for win32;however, we will be compiling and installing this on a GNU/Linux system. First, let’s grab the source from here and decompress:
root@srv-1 src # tar -xjf postgresql-8.0.0beta1.tar.bz2 root@srv-1 postgresql-8.0.0beta1 # ls COPYRIGHT HISTORY Makefile aclocal.m4 configure contrib src GNUmakefile.in INSTALL README config configure.in doc root@srv-1 postgresql-8.0.0beta1 # |
In our case, we want to put this on opt, so we use the prefix option when we configure:
root@srv-1 postgresql-8.0.0beta1 # ./configure --prefix=/opt/pgsql checking build system type... i686-pc-linux-gnu checking host system type... i686-pc-linux-gnu checking which template to use... linux . . . config.status: linking ./src/include/port/linux.h to src/include/pg_config_os.h config.status: linking ./src/makefiles/Makefile.linux to src/Makefile.port root@srv-1 postgresql-8.0.0beta1 # |
Install:
root@srv-1 postgresql-8.0.0beta1 # make install make -C doc install make[1]: Entering directory `/usr/local/src/postgresql-8.0.0beta1/doc' gzip -d -c man.tar.gz | /bin/tar xf - for file in man1/*.1; do \ . . . ving directory `/usr/local/src/postgresql-8.0.0beta1/config' PostgreSQL installation complete. root@srv-1 postgresql-8.0.0beta1 # |
Add a postgres user and prepare a home and data directory:
root@srv-1 root # adduser postgres root@srv-1 root # root@srv-1 root # mkdir /opt/pgsql/data -p root@srv-1 root # root@srv-1 root # chown postgres /opt/pgsql/data Let's initialize the system databases: root@srv-1 root # su - postgres postgres@srv-1 pgsql $ pwd /opt/pgsql postgres@srv-1 pgsql $ ls -l total 28 drwxr-xr-x 2 root root 4096 Aug 17 05:33 bin drwxr-xr-x 2 postgres root 4096 Aug 17 05:36 data drwxr-xr-x 3 root root 4096 Aug 17 05:29 doc drwxr-xr-x 5 root root 4096 Aug 17 05:33 include drwxr-xr-x 3 root root 4096 Aug 17 05:34 lib drwxr-xr-x 4 root root 4096 Aug 17 05:29 man drwxr-xr-x 3 root root 4096 Aug 17 05:33 share postgres@srv-1 pgsql $ postgres@srv-1 pgsql $ bin/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 C. fixing permissions on existing directory data ... ok creating directory data/global ... ok creating directory data/pg_xlog ... ok . . . Success. You can now start the database server using: /opt/pgsql/bin/postmaster -D data or /opt/pgsql/bin/pg_ctl -D data -l logfile start postgres@srv-1 pgsql $ |
Start up the database:
postgres@srv-1 pgsql $ /opt/pgsql/bin/postmaster -D data LOG: database system was shut down at 2004-08-17 05:39:10 PDT LOG: checkpoint record is at 0/A452E0 LOG: redo record is at 0/A452E0; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 492; next OID: 17228 LOG: database system is ready |
Create a test database and poke around:
postgres@srv-1 pgsql $ bin/createdb test CREATE DATABASE postgres@srv-1 pgsql $ postgres@srv-1 pgsql $ bin/psql test Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# test=# show all; name | setting --------------------------------+---------------- add_missing_from | on archive_command | unset australian_timezones | off authentication_timeout | 60 bgwriter_delay | 200 bgwriter_maxpages | 100 bgwriter_percent | 1 block_size | 8192 check_function_bodies | on checkpoint_segments | 3 checkpoint_timeout | 300 . . . test=# \q postgres@srv-1 pgsql $ |
Looks good.
