Between the Software and the Database - Page 2September 23, 2003 Start the InstanceThe last few lines of the execution of the initdb script in Listing 1 gives you the startup commands for using the instance. The particular command is postmaster and the first line of Listing 1 gives you the simplest method of starting the instance. Basically, it just starts the instance against a particular data directory. Listing 4 gives you the options to this command. I would suggest with any command you initiate, always look at the options. They are there for a reason and sometimes have vital information particular to your environment that could alleviate future problems. For instance, one of the checks you might do is verify that the postmaster version is the same as the version of the interactive terminal program (psql). You may not think this is a great problem, but when you get into an upgrade, it will rear its' ugly head. In addition, I notice one option of -D where I can specify the database directory. This tells me I can have multiple postmasters running on the same machine and each of them accessing their own database cluster on one machine. This is where looking at the help for the postmaster command pays off. While it might not be fully obvious for the newcomer to PostgreSQL, but if you were to try to start two instances without options to the postmaster command, you would get an error such as the one in Listing 6. Now you can plainly see that if you were to supply a port option to the postmaster command either through the direct postmaster command or through the pg_ctl script (discussed in a bit) you would successfully get a started instance as I did in Listing 7. Make sure you research these options and plan for proper use of them for your environment. The second line for starting the postmaster in Listing 1 uses a PostgreSQL supplied control script that accepts options to start the instance. This script will always start postmaster in the background and has options to stop, restart, HUP, and report on the status of the postmaster. Listing 5 shows the options to this script. Listing 4 $ postmaster --help postmaster is the PostgreSQL server. Usage: postmaster [OPTION]... Options: -B NBUFFERS number of shared buffers (default 64) -c NAME=VALUE set run-time parameter -d 1-5 debugging level -D DATADIR database directory -F turn fsync off -h HOSTNAME host name or IP address to listen on -i enable TCP/IP connections -k DIRECTORY Unix-domain socket location -N MAX-CONNECT maximum number of allowed connections (default 32) -o OPTIONS pass 'OPTIONS' to each backend server -p PORT port number to listen on (default 5432) -S silent mode (start in background without logging output) --help show this help, then exit --version output version information, then exit Developer options: -n do not reinitialize shared memory after abnormal exit -s send SIGSTOP to all backend servers if one dies Please read the documentation for the complete list of run-time configuration settings and how to set them on the command line or in the configuration file. Report bugs to <pgsql-bugs@postgresql.org>. Listing 5
$ /usr/local/pgsql/bin/pg_ctl --help
pg_ctl is a utility to start, stop, restart, reload configuration files,
or report the status of a PostgreSQL server.
$silence_echo echo "done"
Usage: fi
pg_ctl start [-w] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl restart [-w] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] [-o "OPTIONS"]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]o "postmaster successfully shut down"
fi
Common options:
-D DATADIR location of the database storage area
-s only print errors, no informational messages
-w wait until operation completes
-W do not wait until operation completes
--help show this help, then exityway" 1>&2
--version output version information, then exit
(The default is to wait for shutdown, but not for start or restart.)
fi
If the -D option is omitted, the environment variable PGDATA is used.
fi # stop, restart, reload
Options for start or restart:
-l FILENAME write (or append) server log to FILENAME. The
use of this option is highly recommended.
-o OPTIONS command line options to pass to the postmaster
(PostgreSQL server executable)g. Trying to start postmaster anyway." 1>&2
-p PATH-TO-POSTMASTER normally not necessary
fi
Options for stop or restart:
-m SHUTDOWN-MODE may be 'smart', 'fast', or 'immediate'
if [ -z "$POSTOPTS" ];then
Shutdown modes are:= "start" ];then
smart quit after all clients have disconnectedostmaster.opts.default
fast quit directly, with proper shutdown
immediate quit without complete shutdown; will lead to recovery on restart
fi
Report bugs to <pgsql-bugs@postgresql.org>.
Listing 6 [postgres@koopgate postgres]$ postmaster -D /u04/pgsql/data Lock file "/tmp/.s.PGSQL.5432.lock" already exists. Is another postmaster (pid 1335) using "/tmp/.s.PGSQL.5432"? postmaster: cannot create UNIX stream port Listing 7 [postgres@koopgate postgres]$ postmaster -D /u04/pgsql/data -p 5433 LOG: database system was shut down at 2003-09-16 07:50:05 MDT LOG: checkpoint record is at 0/80193C LOG: redo record is at 0/80193C; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 509; next oid: 16976 LOG: database system is ready Accessing the Database ClusterRemember when creating the database cluster through the initdb command, a template database named template1 is created. In order to verify that we do indeed have an instance up and running we can connect to the template1 database. Listing 8 shows the quick steps on how to connect as well as a few SQL statements to verify that we are alive and well. Listing 8
[postgres@koopgate data]$ psql template1
Welcome to psql 7.3.4, 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
template1=# SELECT version();
version
-------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
template1=# SELECT current_database();
current_database
------------------
template1
(1 row)
template1=# select current_user;
current_user
--------------
postgres
(1 row)
template1=# \q
Earlier in the article, I created a cluster database and specified a new superuser and password. Listing 9 shows the sequence of commands to connect to that instance. What you now need to know is that even though I supplied a password option to this database cluster, Postgres defaults one of its' configuration files to not use passwords. This is of slight concern to me but you can make the change quite easily. What you need to do is edit the client authentication configuration file called pg_hba.conf in your data directory for the database cluster created, in my example '/u03/pgsql/data'. There is documentation in the file as to the different methods of authentication. I changed my method to md5. If you do not change the method, anyone can connect to the database cluster with superuser privileges. Listing 9
[postgres@koopgate data]$ psql -d template1 -U mysuperuser
Password:
Welcome to psql 7.3.4, 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
template1=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
template1=# select current_user;
current_user
--------------
mysuperuser
(1 row)
template1=# \q
Honestly, while I am thrilled about the ease of setup and getting an instance up and running, I am not as quite thrilled to see some of holes in the documentation for items that would be of great concern to DBAs. I had to search the net for some questions I had on file movement and had to display options through the help commands. In addition, it took me about an hour to figure out the starting of a separate instance on a second port. But then again I should heed my own advice, look at the help screen to these commands and search the documents a bit more thoroughly. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
