PostgreSQL basics: Get it running on openSUSE 12.2

This information is of course available from various sources. But it may be useful to someone (other than me) to have a "cheat sheet" with the basics just to get up and running on a vanilla system.


Install:

# zypper install postgresql postgresql-server
Loading repository data...
[more output]

Start the server and verify it's running:

# rcpostgresql start
redirecting to systemctl
# ps ax -o pid,command | grep postgresql
 7281 /usr/lib/postgresql91/bin/postgres -D /var/lib/pgsql/data
 7347 grep --color=auto postgresql
# rcpostgresql status
rcpostgresql status
redirecting to systemctl
postgresql.service - LSB: Start the PostgreSQL master daemon
          Loaded: loaded (/etc/init.d/postgresql)
          Active: active (exited) since Sun, 03 Mar 2013 10:48:46 +0100; 2s ago
         Process: 29815 ExecStart=/etc/init.d/postgresql start (code=exited, status=0/SUCCESS)
          CGroup: name=systemd:/system/postgresql.service

Mar 03 10:48:40 wilbur su[29827]: (to postgres) root on none
Mar 03 10:48:42 wilbur postgresql[29815]: Starting PostgreSQL 9.1.7  ...killed.
Mar 03 10:48:42 wilbur su[29866]: (to postgres) root on none
Mar 03 10:48:46 wilbur postgresql[29815]: ...killed.
Mar 03 10:48:46 wilbur postgresql[29815]: ..done

This last command is the same as systemctl status postgresql.service.

Configure the system to start the PostgreSQL server at every boot. This can be accomplished with YaST. YaST can be run either from the command line, in which case it will run in text-terminal mode, or from the window manager (KDE, Gnome, etc.) in which case it will run as a graphical application. In YaST, choose "System" -> "System Services (Runlevel)". Then find postgresql in the list of services. By default, it is disabled. Change that to enabled and then do 'OK'. The PostgreSQL server will be configured to start automatically at every boot.

If you're too lazy to run YaST, other options would be systemctl enable postgresql.service or chkconfig postgresql on. (While you're at it, also look at man systemctl and man chkconfig.)

To play around with SQL statements, PostgreSQL comes with a client program called psql (in the postgresql91-9.1.6-8.1.x86_64 package). Note that you can't run psql as root, but you can as user postgres:

# psql
psql: FATAL:  role "root" does not exist
# su - postgres
postgres@r400:~> psql
psql (9.1.6)
Type "help" for help.

postgres=#

User postgres can also create, drop a database:

postgres=# CREATE DATABASE testing;
CREATE DATABASE
postgres=# DROP DATABASE testing;
DROP DATABASE
postgres=#

(Note that you can also create and drop databases from the bash prompt using the createdb and dropdb commands. The postgresql91-9.1.6-8.1.x86_64 package also comes with several other commands -- clusterdb, createlang, createuser, droplang, dropuser, pg_basebackup, pg_dump, pg_dumpall, reindexdb, vacuumdb -- see the respective manpages for more information on these.)

psql basics: get help, quit:

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
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
postgres=# \q
#

Recover from a typo. This deserves a closer look. When psql sees "hepl", it assumes you want to enter a multi-line command. Note the change in prompt from postgres=#, to postgres-#. To get back, just hit CTRL-C.

postgres=# hepl
postgres-# ^C
postgres=#

Exit from psql. Works from both the '=' and '-' prompt levels:

postgres=# \q
#

List all databases:

postgres=# \l
                          List of databases
   Name    |  Owner   | Encoding |   Collate ...
[output cropped]

Get help on SQL syntax:

postgres=# \h
Available help:
  ABORT       [output cropped]

Continued in PostgreSQL basics 2: SQL