Understanding how to install and configure PostgreSQL is crucial for database administrators and developers, as it ensures optimal performance, security, and reliability of the database system. Proper installation and configuration lay the foundation for scalable applications, safeguard data integrity, and facilitate seamless integration with various software tools and platforms.
Versioning and History
The first implementation of POSTGRES began back in 1986 and was put into production in 1988. After the user community and demands doubled in size in the early 90s, the POSTGRES Project ended and Postgres95, an open-source SQL language interpreter, was launched. Since then, Postgres has continued to receive widespread adoption, especially with the introduction of the public cloud. With each release, there are significant enhancements and improvements providing more functionality and scalability for customer data. There are various versions of the open-source relational database, and by 1996, PostgreSQL 6.0 was born from the origin product.
In keeping with its yearly update cycle, PostgreSQL 16 ver. 2 was Beta released in June 2023. This newest version is expected to continue PostgreSQL's commitment to being a powerful, open-source, object-relational database system that emphasizes extensibility and standards compliance.
As in previous updates, PostgreSQL consistently made improvements to performance, functionality, and stability. Consequently, version 15 focused on areas such as enhancing partitioning and sharding capabilities, increasing performance for complex queries and improving the database's handling of JSON data. Version 16, on the other hand, has brought additional features for replication between primary/standby servers, better multi-core and parallel query execution, and more advanced maintenance utilities including advanced vacuum capabilities and development support.
It’s incredibly crucial to determine if this is a new installation or an upgrade from a pre-existing PostgreSQL environment. If an upgrade/migration from an earlier version, it is essential to follow through all updates that may render previous release features incompatible with version 16. Please refer to the upgrade documentation to ensure these requirements are addressed before the upgrade. All migrations used with the pg_dumpall (export/import) method or the PostgreSQL upgrade tool should refer to pg_upgrade.
For a new installation, the expected incompatibilities should be less of an issue and can be addressed as the database design and code take shape. All installation files, for an upgrade, migration, or new installation, can be found on the PostgreSQL download site.
Figure 1: PostgreSQL Operating Systems Supported for download, Source: postgresql.org
Along with packages sorted by Operating System (OS), information on how to download previous versions, third-party distributions, and the software catalog, a URL link is provided to a mirror site, which makes the user experience friendlier.
Installation and Configuration Step
General steps for installation of PostgreSQL will use the YUM or APT package manager on a Linux system, dependent on the Linux distribution. The following example uses the YUM package manager and may require some adjustments depending on your environment, PostgreSQL version, and Linux distribution:
1. Update Your System:
Before installing any packages, it's a good practice to update your system's package information.
2. Enable PostgreSQL Repository:
PostgreSQL maintains its own repository, which you need to enable.
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
EL-7-x86_64 with the appropriate version identifier for your Linux Distribution.
3. Install PostgreSQL:
Install the PostgreSQL server and client packages.
sudo yum install -y postgresql-server postgresql-contrib
4. Initialize the Database:
Initialize the PostgreSQL database cluster.
sudo postgresql-setup initdb
5. Start and Enable PostgreSQL:
Start the PostgreSQL service and set it to start on boot.
sudo systemctl start postgresql
sudo systemctl enable postgresql
6. Adjust Firewall Rules (if necessary):
If you have a firewall enabled, you might need to allow access to PostgreSQL. The default port for PostgreSQL is 5432.
sudo firewall-cmd --add-service=postgresql –permanent
sudo firewall-cmd --reload
7. Set a Password for the PostgreSQL User:
By default, PostgreSQL installation creates a system user named 'postgres' and a corresponding PostgreSQL user. You can set a password for the PostgreSQL user.
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'mypassword';"
Remember to replace
'mypassword' with the actual password to secure PostgreSQL.
Please adjust these steps according to the version of PostgreSQL that's available in the repository and the specific Linux distribution you are using. Additionally, be sure to refer to the official PostgreSQL documentation for the most up-to-date instructions.
The template database concept is a valuable and functional feature in PostgreSQL. PostgreSQL is an advanced, open-source relational database system that provides an impressive range of features including the template database feature, which offers a convenient way to control the initial database setup whenever a new database is created.
A template database is essentially a model or a blueprint for creating new databases. Whenever a new database is created in PostgreSQL, it's a clone of a template database. The newly created database contains all the tables, functions, operators, and data present at the time of copying. PostgreSQL has two default template databases: template0 and template1.
template1: This is the default template database for PostgreSQL. Whenever a command is executed to create a new database without specifying any template, PostgreSQL uses
template1. If you modify this database, for example, by installing extensions or changing parameters, all new databases created will inherit those changes, unless you specify otherwise.
template0: This is a backup template. This database contains the standard objects that a fresh install of PostgreSQL would contain. You can use it if you've somehow damaged
template1. You can't connect to
template0 while any other connections exist, which means you can't modify it accidentally or intentionally. This is useful if you want to create a new database that's a clean slate and doesn't include any of the modifications present in
Here's how to create a new database from a template:
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
You can also create your own template databases. For example, if you have a configuration for many databases, you can create a template database with that configuration and then use it as the template for creating new databases.
To create a database as a template, you can do the following:
1. Create a database
2. Connect to the new database and set up the database how you want your template to look
3. Disconnect from the database and then run the following command to set the database as a template:
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'my_template_db';
You can now use your new database as a template:
CREATE DATABASE new_db WITH TEMPLATE my_template_db OWNER dbuser;
The template database feature in PostgreSQL is a powerful tool for managing and streamlining the creation of new databases. It allows you to customize the default settings for new databases and ensure that they are set up in a consistent manner.
Configuring a Database
At this point, you’ve installed PostgreSQL and created a database and the Postgres database user. There are steps recommended for more advanced configurations of PostgreSQL to secure and ensure the system is ready for use, some of which will be reviewed here.
In the first step, we will create a role for the database user to control specific grants for the application:
CREATE ROLE app_role LOGIN PASSWORD 'rolepassword';
The next step is to create the initial schema/user and assign the role:
CREATE USER myuser;
GRANT app_role TO myuser;
Set a password for the user — you can set a password for the newly created role using the
ALTER ROLE SQL command. Replace mypassword with your desired password from the example:
ALTER USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
PostgreSQL supports fine-grained access control through roles and privileges. You can grant privileges to the new role for the database using the
GRANT SQL command:
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
Granting these privileges, log into the database as the new user from the psql utility:
This guide is just a starting point; PostgreSQL is highly configurable and can be tailored for a variety of use cases. You can adjust configurations such as memory allocation, concurrent connections, and database file locations among other parameters in the PostgreSQL configuration file (postgresql.conf) to suit your specific needs.
Tablespaces in PostgreSQL are locations on the hard disk where PostgreSQL stores data files containing database objects such as tables and indexes. This concept allows administrators to control the disk layout of a PostgreSQL instance at a level of granularity beyond what is possible by simply choosing the location of the main data directory. By default, PostgreSQL provides two predefined tablespaces named
pg_default is where your database is stored by default, while
pg_global is used for shared system catalogs that are visible across all databases
Tablespaces are particularly useful in large databases where you need to distribute the storage of data across different storage devices, each potentially having different performance characteristics. For example, you may decide to store frequently accessed tables in a tablespace located on a fast SSD drive, while infrequently accessed tables might be stored in a tablespace located on slower, but more cost-effective, hard disk drives. Alternatively, tablespaces can be used to store a database on a larger, but slower, storage medium if the database has outgrown its current storage. Note that the management and use of tablespaces require some degree of planning and ongoing management to ensure that they meet the needs of your specific use case.