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
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
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
Installing from packages
sudo apt-get install postgresql
Installing from source
Download from http://www.postgresql.org/download/.
Starting and stopping
$ /etc/init.d/postgresql start
Windows
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
Once installed, PostgreSQL listen to the port 5432. So the firewall might need an exception to allow connections, even from localhost.
Creating a user
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
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
- ↑ Requirements for Compilation