# 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

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:
* Installation from Source Code on Unix
* Installation from Source Code on Windows

## Start with the Help of a Prebuild Program

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.

## Examples

Install binaries for Linux (Ubuntu) "PostgreSQL Apt Repository". Retrieved 13 November 2021.
Starting and stopping
Windows
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:
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

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 cluster called data on your disc. The cluster consists of an empty database called postgres (plus two template databases) and a user resp. role called postgres as well.
* A set of Unix programs or a Windows service running 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

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.
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.
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

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 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.

## References

* ↑ Source code in a single packed file (via FTP)
* ↑ Source code in a git repository
* ↑ Requirements for compilation (Unix)
* ↑ Installing from Source
* ↑ Installer
* ↑ Bitnami stacks
* ↑ Download binaries