systems | Help / Postgresql - Using postgresctl to install and start/stop

Installing PostgreSQL
Starting PostgreSQL
Stopping PostgreSQL
Checking PostgreSQL status
Creating a database
Other things to do

Introduction

The standard PostgreSQL install is run as a self-contained DBMS that needs an administrator to add users and grant access. For instructional purposes we've written some scripts that allow you to run your own PostgreSQL server (under your own user id), listening on its own socket/port, with its own database area, and with the database tables, permissions, and personal configuration all maintained by you. This is done with the command postgresctl.

postgresctl install

The command postgresctl install creates a postgresql data directory (~/postgres-data) and creates the initial tables. When prompted enter a database password of your choosing. Remember this password as you will need it to run all the postgresql (psql) commands.

jdash@ix: ~ 16$ postgresctl install
This script is designed to setup an individual postgresql server
When asked for a password, do NOT use your unix password.
This password is to control your database.
Enter new superuser password:
Enter it again:
jdash@ix: ~ 17$

The postgresctl install command also creates a ~/postgres-data/config.sh file. This contains Bourne (or bash) style commands for setting the postgresql environment variables. You can source this file to when you need to execute psql commands.

jdash@ix: ~ 18$ . postgres-data/config.sh
jdash@ix: ~ 19$ echo $PGDATA
/home/users/jdash/postgres-data

postgresctl start

The command postgresctl start starts the server.

jdash@ix: ~ 17$ postgresctl start
Started postgresql server on ix:4403
jdash@ix: ~ 18$

postgresctl stop

The command postgresctl stop will stop your postgresql server.

jdash@ix: ~ 21$ postgresctl stop
Stopping postgres server on ix:4403 in /home/users/jdash/postgres-data

Check the running server

You can use postgresctl status for a quick check of your running server.

jdash@ix: ~ 18$ postgreqctl status
postgres (pid 17271) is listening on ix:4403

If the server is not running the results will look like this:

jdash@ix: ~ 22$ postgresctl status
server is hung

You can also use pg_ctl to check the status of your server.

jdash@ix: ~ 19$ pg_ctl status
pg_ctl: server is running (PID: 17330)
/nfs/local/solaris-local/apps/Databases/postgresql-8.2.1/bin/postgres

Make a new database for your application

The organizational concept each postgresql server works under is that it has a number of databases, and each database will in turn have a number of possibly related schemas, that consist of related tables. Access rights can be different for different databases or schemas. As set up, there is a database named for your username, containing the default schema "public". You can see the tables with the command:

jdash@ix: ~ 19$ . postgres-data/config.sh
jdash@ix: ~ 20$ psql
Password:
Welcome to psql 8.2.1, 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

jdash=# \l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
postgres | jdash | SQL_ASCII
jdash | jdash | SQL_ASCII
template0 | jdash | SQL_ASCII
template1 | jdash | SQL_ASCII
(4 rows)

So to start with, you'll want to create a new database:

jdash=# create database my_db;
CREATE DATABASE

then give it one or more tables:

Now, to add some names to the names table (note that single quotes are important):

my_db=# insert into names values ( 'Jo Don', 'Dash' );
INSERT 0 1
my_db=# select * from names;
first_name | last_name
------------+-----------
Jo Don | Dash
(1 row)

That's enough for basic access to your database.

Other stuff to do...

The details of maintaining a PostgreSQL server are beyond the scope of this document. However, the official PostgreSQL docs are actually fairly understandable, accurate, and up to date.

The postgresql database software is installed on CS machines in /local/apps/postgresql. Included there is the database server daemon, and some utility programs and files.

Manual Pages

	createdb (1)	- create a new PostgreSQL database
	createuser (1)	- define a new PostgreSQL user account
	dropdb (1)	- remove a PostgreSQL database
	dropuser (1)	- remove a PostgreSQL user account
	pg_config (1)	- retrieve information about the installed version of PostgreSQL
	pg_dump (1)	- extract a PostgreSQL database into a script file or other archive file
	pg_restore (1)	- restore a PostgreSQL database from an archive file created by pg_dump
	psql (1)	- PostgreSQL interactive terminal

Help / Postgresql - Using postgresctl to install and start/stop

Table of Contents