PostgreSQL/Download and Installation
Before you download PostgreSQL you must make two crucial decisions. First, decide whether to compile and install PostgreSQL from source code or to install from prebuilt binaries. Second (if you want to use any binary), you must know for which operating system you need the software. PostgreSQL supports most UNIX-based systems (including macOS) as well as Windows.
After you have made those decisions you can download and use the complete source code, an installer, a Bitnami Infrastructure Stack, or the pure binaries.
Start at the Source Code Level Edit
The source code is available as a single packed file  or in a git repository . To install from source you must download it to your local computer and compile it with a C compiler (at least C99-compliant, in most cases people use GCC) to the binary format of your computer. Details of the requirements , the download process, and the compilation steps  are available in the PostgreSQL documentation.
The advantages of working with the source code are that you can read and study it, modify it, or compile it on an exotic platform. But you must have some pre-knowledge and experience in handling specific tasks of your operating system, e.g.: working in a shell, installing additional programs, ... .
The PostgreSQL documentation describes all details of the installation from source in the chapters:
Start with the Help of a Prebuild Program Edit
In opposite to start at the source code level, it is relatively easy to use one of the pre-build programs or scripts. This is the preferred way for beginners. You can choose from several options:
- Installer : This is the most comfortable way to download and install PostgreSQL on your local computer. The installer guides you not only through the installation steps, but also offers the option to install helpful additional tools and drivers. Installers are not available for all versions of all operating systems.
- Bitnami infrastructure stack : Such stacks (WAPP, MAPP, LAPP, and others) offer the complete infrastructure (PostgreSQL, Apache Web Server, PHP) to run Web applications on Windows, macOS, or Linux.
- Pure binaries : This is a listing of operating-specific commands which leads you thru the download and installation process of binaries.
Install binaries for Linux (Ubuntu) "PostgreSQL Apt Repository". Retrieved 13 November 2021.
# Create the file repository configuration: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' # Import the repository signing key: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - # Update the package lists: sudo apt-get update # Install the latest version of PostgreSQL. # If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql': sudo apt-get -y install PostgreSQL
Starting and stopping
sudo /etc/init.d/postgresql start sudo /etc/init.d/postgresql stop
By default, PostgreSQL launches at each reboot so it may consume many resources. To avoid that, just execute services.msc and change the PostgreSQL service to start manually. 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 cluster with all its databases is available. Just press a key to shutdown the service.
More Information Edit
The PostgreSQL wiki offers a lot more information and hints about the installation steps.
After a successful installation, you will have
- The PostgreSQL binaries on your disc.
- A first
clustercalled data on your disc. The cluster consists of an empty
databasecalled postgres (plus two template databases) and a
rolecalled postgres as well.
- A set of Unix programs or a Windows
servicerunning on your computer. These programs/services handle the cluster with all its databases.
By default, PostgreSQL listens to
port 5432. Possibly you must configure your firewall to reflect this situation.
Connect to the Database Edit
After a successful installation, you have a cluster data, a database postgres, the database superuser postgres, and a new operating system user postgres. Login at the operating system level with the new operating system user. In a shell you can connect to the new database via the often used program
psql. psql is a line-mode program similar to a shell and allows you to send SQL commands to the database.
$ # Example in Unix syntax $ su - postgres Password: $ $ # psql --help to see a detailed explanation of psql's options $ # psql [OPTION]... [DBNAME [USERNAME]] $ psql postgres postgres psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1)) Type "help" for help. postgres=# postgres=# \q -- terminate psql with backslash q or ctrl-d $
The default prompt (prefix of every new line) of psql is 'postgres=#'. After you have successfully started it, you can use SQL commands to communicate with the database. Here is an example that creates a new database user with the name 'nancy' - and deletes it afterward.
postgres=# CREATE USER nancy WITH ENCRYPTED PASSWORD 'ab8sxx5F4'; CREATE ROLE postgres=# postgres=# DROP USER nancy; -- delete the user DROP ROLE postgres=#
The database responds to every SQL command indicating its successful execution or an error. In the previous example
CREATE ROLE means that the user is created.
Separation of Concerns Edit
Please recap what you have so far: a
cluster data, a
database postgres, a
user postgres. Furthermore, PostgreSQL divides every database into logical units which are called
schema. Most objects reside in such a schema. The default schema is named public and exists in every database. The same applies to some special schemas where system information is stored. As far as you don't explicitly use schema-names, the schema public is utilized by default. This means that a
CREATE TABLE t (column_1 INTEGER); command will create the table t in schema public.
We recommend avoiding the schema public for your data. Because public exists in every database, some tools use it to store their data there. Create and work in your own schema to have a clear distinction between system-, tools-, and user-data.
Second, avoid working with user postgres. This user account has very strong privileges and you should rarely use it. Create a user who acts as the stakeholder for your data, views, functions, trigger, etc. .
The following script creates a new user and its schema.
$ # start 'psql' as the original 'postgres' user with its strong priviledges $ psql postgres postgres postgres=# -- the owner of the new schema shall be 'finance_master' postgres=# CREATE USER finance_master WITH CREATEROLE LOGIN ENCRYPTED PASSWORD 'xxx'; CREATE ROLE postgres=# -- the new schema 'finance' for your data postgres=# CREATE SCHEMA finance AUTHORIZATION finance_master; CREATE SCHEMA postgres=# -- change 'search_path' (description of search_path: see below) postgres=# ALTER ROLE finance_master SET search_path = finance, public; ALTER ROLE postgres=# \q
Start psql with the new user finance_master. We want him to work in schema finance, but every connection between psql and PostgreSQL acts at the database-level. It's not possible to specify an individual schema for a connection. Therefore PostgreSQL has implemented a mechanism called
search_path. It simplifies the switching between schemas.
search_path contains a list of schema names. Whenever you omit a schema name, this list is consulted to decide which schema to use. For our user finance_manager we have defined in the above
ALTER ROLE command that he shall work in schema finance and - if there is no hit for his SQL command e.g. for a
SELECT - the schema public is consulted next.
$ # -- first parameter of psql: database second parameter: user nothing for schema $ psql postgres finance_master postgres=# -- create a table postgres=# CREATE TABLE t1 (column_1 INTEGER); -- table will be in schema 'finance' because of the 'search_path' definition CREATE TABLE postgres=# -- you can use the schema name explicitly postgres=# CREATE TABLE finance.t2 (column_1 INTEGER); -- table will be in schema 'finance' as well CREATE TABLE postgres=# -- it's possible to overwrite 'search_path' by using the schema name explicitly postgres=# CREATE TABLE public.t3 (column_1 INTEGER); -- table will be in schema 'public' CREATE TABLE postgres=# postgres=# \d -- this command lists schema, table, and owner names List of relations Schema | Name | Type | Owner ---------+---------+-------+---------------- finance | t1 | table | finance_master finance | t2 | table | finance_master public | t3 | table | finance_master postgres=#