PostgreSQL/Download and Installation

From Wikibooks, open books for an open world
Jump to navigation Jump to search
Featured Platforms PostgreSQL
Download and Installation
Terms


Before you download PostgreSQL you must make two crucial decisions. First, decide whether to compile and install PostgreSQL from source or to install from prebuilt binaries. To install from source code you must compile the source with a C compiler (at least C89-compliant, in most cases people use GCC) to the binary format of your computer. Details on compilation options are available in the PostgreSQL documentation [1]. Second, you must know for which operating system you need the software. PostgreSQL supports most UNIX-based systems (including macOS) as well as Windows - both in 32- and 64-bit versions.

Download[edit]

After you have made the above decisions you can download the source code and/or the binaries from the official postgreSQL download page and its subpages. For some operating systems you will find a graphical installer which leads you through the subsequent installation steps. For the same or other operating systems the DB tool pgAdmin will also be downloaded as part of the PostgreSQL DBMS download. The pgAdmin tool helps you doing your daily work thru its graphical interface.

There are different versions available: the actual release, old releases and the upcoming release.

Installation[edit]

Installation steps vary depending on the chosen operating system. In the simplest case the above mentioned graphical installer hides the details. The PostgreSQL installation guide wiki and documentation leads you thru all necessary steps for your operating system.

If you install from source code, details are explained for Unix systems and Windows.

After a successful installation you will have

  • The PostgreSQL binaries on your disc.
  • A first database cluster called 'main' on your disc. The database cluster consists of an empty database called 'postgres' (plus two template databases) and an user/role called 'postgres' as well.
  • A set of Unix programs or a Windows service running on your computer. These programs/service handle the database cluster as an unit.

Linux[edit]

Installing from packages[edit]

sudo apt-get install postgresql

Installing from source[edit]

Download from http://www.postgresql.org/download/.

Starting and stopping[edit]

$ /etc/init.d/postgresql start

Windows[edit]

By default, PostgreSQL launches at each reboot so it can consume too many resources. To avoid that, just execute services.msc and set the PostgreSQL service in manual start.

Then, create a file PostgreSQL.cmd containing:

net start postgresql-x64-9.5
pause
net stop postgresql-x64-9.5

As long as this script is launched as an administrator, the database will work. Just press a key to shutdown it after utilization.

Firewall[edit]

Once installed, PostgreSQL listen to the port 5432. So the firewall might need an exception to allow connections, even from localhost.

Creating a user[edit]

The following command creates a new user with superuser privileges:

$ createuser -U postgres -s <some username>

Creating a new user is a database operation, so it can only be done by an existing database user. You need to specify which database user to use (otherwise it will default to using a database user with the same name as your current UNIX user, which is unlikely to be correct). Default installs typically have a user called postgres that can be used for this.

To set a password for the newly created user, log in to the database using an account with superuser privileges, and issue the commands:

Note: '=#' is the psql command-prompt.

$ psql
postgres=# ALTER USER <username> WITH ENCRYPTED PASSWORD 'secret';

First steps[edit]

You can create your tables, views, functions etc. in this database 'postgres' and work with the user 'postgres'. But this approach is not recommended. The user 'postgres' has very high privileges by default and the database 'postgres' is sometimes used by tools and third party programs as a container for temporary data. You are encouraged to define your own database, one user who acts as the owner of the database and some application users.

As a first step start psql with user 'postgres' and create your own users. Please notice, that 'users' respective 'roles' are global objects which are known by all databases within the cluster, not only within a certain database. But users/roles have specific rights within each database.

$ psql
postgres=#
postgres=# -- the future owner of the new database shall be 'finance_master' with DDL and DML rights
postgres=# CREATE ROLE finance_master;
CREATE ROLE
postgres=# ALTER  ROLE finance_master WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION ENCRYPTED PASSWORD 'xxx';
ALTER ROLE
postgres=# -- one user for read/write and one for read-only access (no DDL rights)
postgres=# CREATE ROLE rw_user;
CREATE ROLE
postgres=# ALTER  ROLE rw_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION ENCRYPTED PASSWORD 'xxx';
ALTER ROLE
postgres=# CREATE ROLE ro_user;
CREATE ROLE
postgres=# ALTER  ROLE ro_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION ENCRYPTED PASSWORD 'xxx';
ALTER ROLE
postgres=#

Next, create a new database 'finance_db'. You can do this as user 'postgres' or as the previously created 'finance_master'.

postgres=#
postgres=# CREATE DATABASE finance_db
postgres=#    WITH OWNER = finance_master
postgres=#    ENCODING   = 'UTF8'
postgres=#    LC_COLLATE = 'en_US.UTF-8'
postgres=#    LC_CTYPE   = 'en_US.UTF-8';
CREATE DATABASE
postgres=# 

As the last step you have to delegate the intended rights to the users/roles. This is a little tricky because PostgreSQL uses an elaborated role system where every role inherits rights from the implicit 'public' role.

postgres=#
postgres=# \connect finance_db
finance_db=# -- revoke schema creation from role 'public' because all roles inherit their rights from 'public'
finance_db=# REVOKE CREATE ON DATABASE finance_db FROM public;
REVOKE
finance_db=# -- same: revoke table creation
finance_db=# REVOKE CREATE ON SCHEMA public FROM public;
REVOKE
finance_db=# -- grant only DML rights to 'rw_user', no DDL rights like 'CREATE TABLE'
finance_db=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO rw_user;
GRANT
finance_db=# GRANT USAGE                          ON ALL SEQUENCES IN SCHEMA public TO rw_user;
GRANT
finance_db=# -- grant read rights to the read-only user
finance_db=# GRANT SELECT                         ON ALL TABLES    IN SCHEMA public TO ro_user;
GRANT
postgres=#

References[edit]

  1. Requirements for Compilation [1]


Featured Platforms PostgreSQL
Download and Installation
Terms