How do I install PostgreSQL in Debian/Ubuntu?

Question

I'm following these instructions, however I can only get to step 17.2.

Despite installing postgresql successfully via the

sudo apt-get install postgresql

command, upon running

initdb -D /usr/local/pgsql/data

Ubuntu tells me that it 'initdb' isn't installed. The instructions tell me this command is installed by

sudo apt-get install postgresql

so what's going on?

Answer 1

If you haven't set up the environment variables, then you need to specify the full path to your executables.

What version of Postgresql are you using? You show a link to 8.3 which is many years old. These docs are current: http://www.postgresql.org/docs/current/static/creating-cluster.html

Did you install Postgresql with an up-to-date package, a la http://www.postgresql.org/download/linux/ubuntu/ ?

When installing 9.2 on Ubuntu, I added the following lines to the ~postgres/.bash_profile

export PATH=/usr/lib/postgresql/9.2/bin:$PATH:/usr/sbin
export PGDATA=/var/lib/postgresql/9.2/main
export PGPORT=5432

Where "/usr/lib/postgresql/9.2/bin" is the path to your 9.2 executables. That made the installation a bit smoother for me.

Answer 2

The instructions you link to are out of date. They are for version 8.3 which is no longer supported. As of the time of writing this the oldest supported version is 8.4.

Although even 8.4 is still supported, if you're doing a new install it's recommended that you install a newer version. The standard Ubuntu 12.04 repositories currently have PostgreSQL version 9.1. An alternative (listed below) is to use the PGDG APT repositories. They're more up to date and allow you to also install 9.2 or 9.3 (the latest stable release as of the time of writing this).

Installing PostgreSQL on Ubuntu is really easy. The instructions below install PostgreSQL 9.3. To install a different version, just change the PG_VERSION variable name to the version you'd like to install.

Also listed below is how to install the contrib module which allows you to install a lot of the more popular extensions (like hstore) via CREATE EXTENSION ....

# Retrieve and install the GPG key PGDG:
PG_REPO_APT_SOURCE=/etc/apt/sources.list.d/pgdg.list
if [ ! -f "$PG_REPO_APT_SOURCE" ]
then
  # Add PG apt repo:
  echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > "$PG_REPO_APT_SOURCE"

  # Add PGDG repo key:
  wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
fi

# Update your package lists
apt-get update

# Edit this to the version you'd like to install, e.g. 8.4, 9.0, 9.1, 9.2, or 9.3
PG_VERSION=9.3

# Install PostgreSQL
apt-get install "postgresql-${PG_VERSION}"

# (Optional) Install PostgreSQL Contrib Modules
apt-get install "postgresql-contrib-${PG_VERSION}"

Note that you don't need to do anything special like run initdb manually after installing it this way. That will all be done for you by the installer.

Answer 3

Installing PostgreSQL

It's easy on Debian/Ubuntu

sudo apt-get install postgresql

Simple Configuration

This is a copy of my original post here

There are two methods you can use. Both require creating a user and a database.

1. Using createuser and createdb,

   $ sudo -u postgres createuser -s $USER
   $ createdb mydatabase
   $ psql -d mydatabase
   

2. Using the SQL administration commands, and connecting with a password over TCP

   $ sudo -u postgres psql postgres
   

   And, then in the psql shell

   CREATE ROLE myuser LOGIN PASSWORD 'mypass';
   CREATE DATABASE mydatabase WITH OWNER = myuser;
   

   Then you can login,

   $ psql -h localhost -d mydatabase -U myuser -p <port>
   

   If you don't know the port, you can always get it by running the following, as the postgres user,

   SHOW port;
   

   Or,

   $ grep "port =" /etc/postgresql/*/main/postgresql.conf
   

Sidenote: the postgres user

I suggest NOT modifying the postgres user.

1. It's normally locked from the OS. No one is supposed to "log in" to the operating system as postgres. You're supposed to have root to get to authenticate as postgres.
2. It's normally not password protected and delegates to the host operating system. This is a good thing. This normally means in order to log in as postgres which is the PostgreSQL equivalent of SQL Server's SA, you have to have write-access to the underlying data files. And, that means that you could normally wreck havoc anyway.
3. By keeping this disabled, you remove the risk of a brute force attack through a named super-user. Concealing and obscuring the name of the superuser has advantages.