PostgreSQL Free Database Management System
Open Source Object-relational Database Management System
Use PostgreSQL relational DBMS to ensure reliability, concurrency and consistency for your apps. It also offers disaster recovery and extensibility features.
Overview
PostgreSQL is a powerful, open source object-relational database management system. It extends the SQL language and adds many additional features. PostgreSQL is the most advanced DBMS software because of its proven architecture, reliability, data integrity, robust feature set as well as extensibility, and disaster recovery. Moreover, the dedication of its open source community consistently delivers performant and innovative solutions. PostgreSQL runs on all major operating systems. It has powerful add-ons such as the popular PostGIS geospatial database extender.
PostgreSQL open source rdbms comes with many features aimed to help developers develop applications. It protects data integrity and makes a fault-tolerant environment. Moreover, PostgreSQL is highly extensible, as you can define your custom data types, build out custom functions, even write code from different programming languages without recompiling the database. PostgreSQL supports most of the features required by the SQL standard but sometimes with slightly differing syntax or function.
System Requirements
- Ubuntu 18.04 server
- Access to a command-line/terminal window
- A non-root user with sudo permissions
- A basic firewall
Features
- Multi-version concurrency control
- Geometric primitives
- IPv4 and IPv6 addresses
- Classless Inter-Domain Routing blocks and MAC addresses
- XML supporting XPath queries
- Universally unique identifier
- JavaScript Object Notation (JSON), and a faster binary JSONB
- Referential integrity constraints including foreign key constraints, column constraints, and row checks
- Binary and textual large-object storage
- Tablespaces
- Per-column collation
- Online backup
- Point-in-time recovery, implemented using write-ahead logging
- In-place upgrades with pg_upgrade for less downtime
- Built-in interfaces libpq (the official C application interface) and ECPG (an embedded C system)
- Third-party libraries for connecting to PostgreSQL
- Provides an asynchronous messaging system that is accessed through the NOTIFY, LISTEN and UNLISTEN commands
- Inner, outer (full, left and right), and cross joins
- Sub-selects
- Correlated sub-queries
- Regular expression
- Common table expressions and writable common table expressions
- Encrypted connections via Transport Layer Security (TLS)
- Two-phase commit
- Oversized-Attribute storage technique
- Embedded SQL implementation using preprocessor
- Open Source
Installation
Follow these steps to Install PostgreSQL on Ubuntu 18.04:
- To install PostgreSQL, first refresh your server’s local package index:
sudo apt update
- Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:
sudo apt install postgresql postgresql-contrib
- During installation, a user account postgres is created. Switch over to the postgres account on your server by typing:
sudo -i -u postgres
- Then you can access the Postgres prompt by typing:
psql
- To exit out of the PostgreSQL prompt, run the following:
\q
- To return to your regular system user, run the following exit command:
exit
- If you are logged in as the postgres account, you can create a new role by running following command:
createuser --interactive
- Now create the database with following createdb command:
createdb firstdb
- Then open a Postgres Prompt with the New Role
sudo adduser firstuser
- Once this new account is available, you can connect to the database by typing:
sudo -u sammy psql
- Finally check your current connection information by typing following command:
\conninfo
- It should give following output:
You are connected to database "firstdb" as user "firstuser" via socket in "/var/run/postgresql" at port "5432".
