![]()
|
Getting Started With PostgreSQL
I've sucessfully installed PostgreSQL on Linux, FreeBSD, NetBSD, OpenBSD, Solaris and SCO OpenServer. FreeBSD, OpenBSD, NetBSD and most Linux distributions come with PostgreSQL and give you the option of installing it when you install the rest of the system. If you want or need to install it afterward, follow the instructions below. RPM-based LinuxTo install PostgreSQL on an RPM-based Linux distribution like RedHat, Mandrake or TurboLinux, acquire the postgresql, postgresql-server, postgresql-devel and postgresql-libs RPMS from RPMFind or possibly from the CD's that came with your distribution and install them using rpm -i. Debian LinuxIf you have configured apt as illustrated in the APT-HOWTO, run apt-get install postgresql and apt-get install postgresql-dev. If your distribution came with PostgreSQL, you may be promted to enter a CD. If not, it will be downloaded from the internet. When the commands complete, PostgreSQL will be installed. Slackware LinuxIf there are PostgreSQL packages for Slackware, I've never been able to find them. I've always had to compile from source. FreeBSDIf you have an internet connection, run pkg_add -r postgresql. When the command completes, PostgreSQL will be installed. You can also install PostgreSQL from the Ports CD(s) that came with your distribution using /stand/sysinstall. OpenBSDThe postgresql package is available from ftp.openbsd.org or on CD's that came with your distribution. You can install it using pkg_add. Once the package is installed, you should run the following commands: useradd -m postgresNetBSD The postgresql and postgresql-clients packages are available from ftp.netbsd.org or on CD's that came with your distribution . You can install them using pkg_add. SCO OpenServerFor SCO OpenServer, PostgreSQL packages are available from the Skunkware ftp server. SCO OpenServer packages are often called VOL's because they come as a set of files named VOL.000.000, VOL.000.001, etc. These VOLS can be installed using the Software Manager (custom). SolarisIf there are PostgreSQL packages for Solaris, I've never been able to find them. I've always had to compile from source. Compiling From SourceIf you want to compile PostgreSQL from source, it should compile cleanly on all of the platforms mentioned above with the exception of SCO OpenServer. I have never been able to get it to compile on SCO. The source code is available from the PostgreSQL site. You should create a user named postgres and assign it a password, then build and install PostgreSQL using that user. By default, PostgreSQL installs itself in /usr/local/pgsql. Note that you should add /usr/local/pgsql/bin to your PATH environment variable and /usr/local/pgsql/lib to your LD_LIBRARY_PATH environment variable. After compiling and installing PostgreSQL, you should log in as the postgres user and run initdb -D /usr/local/pgsql/var to initialize the database. Starting the Database at Boot TimeThe package distributions of PostgreSQL install a script which starts the database at boot time. If you compiled from source, you'll need to install a script like the following to start the database at boot time. #!/bin/sh case "$1" in start) su -l postgres -s /bin/sh -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/var -p /usr/local/pgsql/bin/postmaster start > /dev/null 2>&1" < /dev/null ;; stop) kill `ps -efa | grep postmaster | grep -v grep | awk '{print $2}'` ;; *) echo $"Usage: $0 {start|stop}" exit 1 esac exit 0 Install this script and run it with the "start" option to start up the database. Running it with the "stop" option shuts the database down. To access a database, it must be running. Initial ConfigurationIt's necessary to perform administrative tasks as the postgres user. Assigning it a password makes this easier. By default, PostgreSQL is fairly insecure. Any user on the local machine can connect to the database without supplying a password. To begin securing PostgreSQL, log in as postgres. By default, PostgreSQL has a database called template1 containing privileges and other housekeeping data and an adminstrative user named postgres. The postgres database user initially has no password. To assign it a password, run a command like the following. This command assigns the postgres user the password newpassword. For PostgreSQL 7:psql -c "ALTER USER postgres WITH PASSWORD 'newpassword'" -d template1For PostgreSQL 6:psql -c "ALTER USER postgres WITH PASSWORD newpassword" -d template1 Now edit the pg_hba.conf file (usually found in /var/lib/pgsql, /var/lib/pgsql/data or /usr/local/pgsql/var/data) and look for lines like the following: local all trust host all 127.0.0.1 255.255.255.255 trust Modify these lines to read: local all password host all 127.0.0.1 255.255.255.255 password By default, PostgreSQL listens on a unix socket, but not on an inet socket. Thus, clients running on the local machine can connect to the database, but clients running on remote hosts cannot. To change this behavior, I had to modify the pg_ctl script and add the -i option to the postmaster startup command. There doesn't appear to be an easier way. Additionally, you must add a line to the pg_hba.conf file to allow remote hosts to access local databases. The following line allows all hosts in the 192.168.2.0 class C network to access all databases on the local machine, using password authentication. Creating a Databasehost all 192.168.2.0 255.255.255.0 password After installation and database initialization, PostgreSQL is ready to use but to do any useful work, you'll have to create a database. In PostgreSQL terminology, a database is a collection of files managed by a program called postmaster. Technically, any user can create a database and run a postmaster to manage it, but for simplicity's sake, we'll just make our database owned by the postgres user. To create a database named testdb with an administrator named postgres, log in as postgres and run the following command. For PostgreSQL 7:psql -U postgres -c "CREATE DATABASE testdb" -d template1For PostgreSQL 6: Once the database has been created, you can log into it as follows. For PostgreSQL 7:psql -U postgres -d testdbFor PostgreSQL 6: Once logged in, you can create, drop and modify tables and run queries in the database. PostgreSQL authentication is a complex topic. Lines in the pg_hba.conf file define access rules and restrictions for hosts and databases. For more information, consult the PostgreSQL online documentation. To create database users, use commands like the following. This command creates a user named testuser with password testpassword. For PostgreSQL 7:For PostgreSQL 6:psql -U postgres -c "CREATE USER testuser WITH PASSWORD 'testpassword'" -d testdb Password: newpasswordpsql -u -c "CREATE USER testuser WITH PASSWORD testpassword -d testdb Username: postgres Password: newpassword You can now log into the database as testuser using the following command. For PostgreSQL 7:psql -U testuser -d testdbFor PostgreSQL 6: If you want to drop a user, you can do so. The following command drops a user named testuser. For PostgreSQL 7:For PostgreSQL 6:psql -U postgres -c "DROP USER testuser" -d testdb Password: newpasswordpsql -u -c "DROP USER testuser" -d testdb Username: postgres Password: newpassword If you want to drop the database, you can do so with the following command. For PostgreSQL 7:For PostgreSQL 6:psql -U postgres -c "DROP DATABASE testdb" -d template1 Password: newpasswordpsql -u -c "DROP DATABASE testdb" -d template1 Username: postgres Password: newpassword This should be enough to get you started. To set up more complex configurations, consult the online documentation. Accessing a DatabaseAccessing a PostgreSQL database using the psql client tool is simple. For example, to access a database called testdb on the local machine as the testuser user with password testpassword, use the following command. For PostgreSQL 7:psql -U testuser -d testdbFor PostgreSQL 6: If you want to access a database on a remote machine, say on testhost, use the -h option as follows. For PostgreSQL 7:psql -U testuser -d testhost -d testdbFor PostgreSQL 6: Once you're connected to the database, the psql client prompts you to enter a query. Queries may be split across multiple lines. To run a query, end it with a semicolon or type \g on the next line. To exit, type \q. A sample psql session follows. [user@localhost user]$ psql -U testuser testdb Password: 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 testdb=> create table testtable ( testdb(> col1 char(40), testdb(> col2 integer testdb(> ); CREATE testdb=> select * from pg_tables where tableowner='testuser'; tablename | tableowner | hasindexes | hasrules | hastriggers -----------+------------+------------+----------+------------- testtable | testuser | f | f | f (1 row) testdb=> \d testtable Table "testtable" Attribute | Type | Modifier -----------+---------------+---------- col1 | character(40) | col2 | integer | testdb=> insert into testtable values ('hello',50); INSERT 468520 1 testdb=> insert into testtable values ('hi',60); INSERT 468521 1 testdb=> insert into testtable values ('bye',70); INSERT 468522 1 testdb=> select * from testtable; col1 | col2 ------------------------------------------+------ hello | 50 hi | 60 bye | 70 (3 rows) testdb=> update testtable set col2=0 where col1='hi'; UPDATE 1 testdb=> select * from testtable; col1 | col2 ------------------------------------------+------ hello | 50 bye | 70 hi | 0 (3 rows) testdb=> delete from testtable where col2=50; DELETE 1 testdb=> select * from testtable; col1 | col2 ------------------------------------------+------ bye | 70 hi | 0 (2 rows) testdb=> drop table testtable; DROP testdb=> \qAccessing a Database With SQL Relay Accessing PostgreSQL from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called postgresqltest. This instance connects to the testdb database running on the remote server testhost as the user testuser with password testpassword. <!DOCTYPE instances SYSTEM "sqlrelay.dtd"> <instances> <instance id="postgresqltest" port="9000" socket="/tmp/postgresqltest.socket" dbase="postgresql" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass"> <users> <user user="postgresqltest" password="postgresqltest"/> </users> <connections> <connection connectionid="postgresqltest" string="host=testhost;db=testdb;user=testuser;password=testpass" metric="1"/> </connections> </instance> </instances> Now you can start up this instance with the following command. sqlr-start -id postgresqltest To connect to the instance and run queries, use the following command. sqlrsh -id postgresqltest The following command shuts down the SQL Relay instance. sqlr-stop postgresqltest |