Version 7.2 to 8.2: Included features like Schema support, Nonblocking VACUUM, Roles and dblink – 2002-2006 PostgreSQL 8.4 released in 2009 PostgreSQL 9.0 released in 2010. Linux Tutorial Postgresql Database And List Detail Er Diagram View All In One Database Manager The Anatomy Of Postgresql Part 2 Database Objects Postgres. Postico A Modern Postgresql Client For The Mac Retrieving Table Schemas 6 1 Apache Hive 3 Tables.
Postico 1 3 2 – A Modern Postgresql Client Tutorial Download
PostgreSQL 13, the latest release of the Postgres database software, comes with many under-the-hood improvements.While being the most popular and versatile open-source RDBMS around, it is notthe easiest to setup and get started. Serial 2 0 0 6. Read on to learn how you can get goingwith the latest version of Postgres on the latest LTS version of the Ubuntuserver.
Installation
Ubuntu 20.04 comes with Postgres 12 from it's universe repository. Since wewant version 13, we can directly use the PostgreSQL project's officialAPT repository.This repository contains binaries for Ubuntu 20.04, and also includes packagesfor various extensions that you might want to install later.
Let's setup the repository like this (note that 'focal' is the code name forUbuntu 20.04):
We can now install the PostgreSQL server and other command-line tools using:
The installation does a few things:
- It installs the PostgreSQL server, utilities and a command-line client calledpsql.
- It creates a Linux system user called postgres. All data files are ownedby this user, and all processes run as this user.
- It creates a database cluster (see below). In this cluster, it creates adatabase, also called postgres.
- It creates one PostgreSQL user (not the Linux system user), also calledpostgres. This PostgreSQL user has superuser privileges.
You can see this is beginning to get confusing!
Database Clusters
In Postgres terms, we now have a single database cluster up and running. Asingle database cluster can contain one or more databases. In the databasecluster that we now have, there is a database called 'postgres'. (There arealso a couple of 'template' databases that we can ignore for now.)
A database cluster is managed by a main postgres process called the postmaster.It spawns various child processes that either perform various system tasks orhandle incoming client connections. Have a look at the currently runningprocesses:
Here the postmaster process is 4880 and it has spawned 6 child processes thathandle various housekeeping activities. You can also see the location of thecluster (/var/lib/postgresql/13/main) and the location of the configurationfile (/etc/postgresql/13/main/postgresql.conf).
Reloading and Restarting
At various times, you may need to reload or restart your Postgres server.Reloading causes Postgres to re-examine it's configuration files and apply thechanges. If there are no changes to the configuration files, nothing bad happens.Reloading does not disturb the currently connected clients. To reload yourPostgres server, you can do:
Some configuration changes will take effect only after you restart the server.This is more disruptive and will disconnect all connected clients. To restart,you can:
Log Files
As you can see, there is a systemd service called postgresql that you canuse to control the postmaster. If the service does not start, you can checkit's status to check for error messages:
The PostgreSQL server writes a log file, which you can check for more detailederror messages. This file is located at /var/log/postgresql/postgresql-13-main.log:
Connecting to Your Postgres Server
Now that we have our server up and running, let's try to connect to it. By default,the server listens only for:
- TCP connections from 127.0.0.1 on port 5432, and
- Unix domain sockets in /var/run/postgresql
Because of the default configuration, the only way to connect to the serverright now is via the Unix socket from a process that is running as thesystem user postgres. Let's run the standard interactive client psql likethis:
Here we're running psql as the system user postgres ('sudo -u postgres psql')and connecting to the database called 'postgres' (the last 'postgres' on thecommand-line.) The 'postgres=#' prompt indicates the name of the currentlyconnected database ('postgres') and that we have superuser privileges ('#' as opposed to'$').
The connection happened via Unix sockets (this is the default method in psql).Since by default the postgres user does not have a password and the defaultconfiguration requires password authentication for TCP connections, it is notpossible to connect over 127.0.0.1:5432 right now.
Postico 1 3 2 – A Modern Postgresql Client Tutorial Download
PostgreSQL 13, the latest release of the Postgres database software, comes with many under-the-hood improvements.While being the most popular and versatile open-source RDBMS around, it is notthe easiest to setup and get started. Serial 2 0 0 6. Read on to learn how you can get goingwith the latest version of Postgres on the latest LTS version of the Ubuntuserver.
Installation
Ubuntu 20.04 comes with Postgres 12 from it's universe repository. Since wewant version 13, we can directly use the PostgreSQL project's officialAPT repository.This repository contains binaries for Ubuntu 20.04, and also includes packagesfor various extensions that you might want to install later.
Let's setup the repository like this (note that 'focal' is the code name forUbuntu 20.04):
We can now install the PostgreSQL server and other command-line tools using:
The installation does a few things:
- It installs the PostgreSQL server, utilities and a command-line client calledpsql.
- It creates a Linux system user called postgres. All data files are ownedby this user, and all processes run as this user.
- It creates a database cluster (see below). In this cluster, it creates adatabase, also called postgres.
- It creates one PostgreSQL user (not the Linux system user), also calledpostgres. This PostgreSQL user has superuser privileges.
You can see this is beginning to get confusing!
Database Clusters
In Postgres terms, we now have a single database cluster up and running. Asingle database cluster can contain one or more databases. In the databasecluster that we now have, there is a database called 'postgres'. (There arealso a couple of 'template' databases that we can ignore for now.)
A database cluster is managed by a main postgres process called the postmaster.It spawns various child processes that either perform various system tasks orhandle incoming client connections. Have a look at the currently runningprocesses:
Here the postmaster process is 4880 and it has spawned 6 child processes thathandle various housekeeping activities. You can also see the location of thecluster (/var/lib/postgresql/13/main) and the location of the configurationfile (/etc/postgresql/13/main/postgresql.conf).
Reloading and Restarting
At various times, you may need to reload or restart your Postgres server.Reloading causes Postgres to re-examine it's configuration files and apply thechanges. If there are no changes to the configuration files, nothing bad happens.Reloading does not disturb the currently connected clients. To reload yourPostgres server, you can do:
Some configuration changes will take effect only after you restart the server.This is more disruptive and will disconnect all connected clients. To restart,you can:
Log Files
As you can see, there is a systemd service called postgresql that you canuse to control the postmaster. If the service does not start, you can checkit's status to check for error messages:
The PostgreSQL server writes a log file, which you can check for more detailederror messages. This file is located at /var/log/postgresql/postgresql-13-main.log:
Connecting to Your Postgres Server
Now that we have our server up and running, let's try to connect to it. By default,the server listens only for:
- TCP connections from 127.0.0.1 on port 5432, and
- Unix domain sockets in /var/run/postgresql
Because of the default configuration, the only way to connect to the serverright now is via the Unix socket from a process that is running as thesystem user postgres. Let's run the standard interactive client psql likethis:
Here we're running psql as the system user postgres ('sudo -u postgres psql')and connecting to the database called 'postgres' (the last 'postgres' on thecommand-line.) The 'postgres=#' prompt indicates the name of the currentlyconnected database ('postgres') and that we have superuser privileges ('#' as opposed to'$').
The connection happened via Unix sockets (this is the default method in psql).Since by default the postgres user does not have a password and the defaultconfiguration requires password authentication for TCP connections, it is notpossible to connect over 127.0.0.1:5432 right now.
Allowing Incoming Connections From an Internal Network
First let's change the configuration to allow connections from an internalnetwork. Assuming our server's IP on this network is 10.1.2.3, we can editthe main configuration file at /etc/postgresql/13/main/postgresql.conf andchange the lines:
to:
We also need to tell Postgres to use password authentication for connectionscoming in from these networks. For this, edit another configuration filecalled /etc/postgresql/13/main/pg_hba.conf and change the line:
to:
(Assuming the internal network is 10.1.0.0/16.)
We've also changed the default md5 method to the newer and more securescram-sha-256. All other occurances of md5 in the file should also bereplaced with scram-sha-256. If your application or database driver does notsupport this method, continue to use the md5 method instead.
For these changes to take effect, you need to restart the server:
Creating a Regular User and Database
We're almost there!
Postico 1 3 2 – A Modern Postgresql Client Tutorial For Beginners
We can now create a regular user that our application can connect as, and adatabase over which it has full control. Connect as the superuser postgreslocally from the server machine to do this:
(Omit the first command if you want to use md5 instead.) This created a usercalled alice with the password s3cr3tp@ss. Let's also create a databasewhich this user will own:
The database is called app1. Since alice owns this database, all operationswithin the database (like creating tables, inserting rows) are allowed if theapplication connects as the user alice.
Let's try connecting as alice, over the network:
Cool! We're now connected to the database app1 as the user alice.
Deleting Databases, Backing up and Restoring
Here are a few tricks that can help as you continue working with your Postgresserver:
Deleting a database
You can delete the database you just created ('app1'), like this:
Note that you need to switch to another database first using the 'c' commandof psql.
To create another database, or to recreate app1, connect as the superuser and do'CREATE DATABASE' like before.
Backup up the database
The easiest way to backup the data in your database is to use pg_dump likethis:
This creates a SQL file called 'backup.sql' that contains all the SQL commandsrequired to recreate the schema and data in the database app1, in text format.You can execute these commands in any database, and the schema and data will getpopulated into that database.
Read more about pg_dump here.
Restoring data
The SQL command file you created above can be restored like so:
Note that we restored the schema and data into another database, app2. The'i' command of psql lets you execute the SQL commands from a file.
Postico 1 3 2 – A Modern Postgresql Client Tutorial Pdf
Next Steps
There are a whole bunch of articles, tutorials, videos and courses out thereto help you get more proficient with PostgreSQL. Do spend some time however,with the official documentation here,that provides authoritative and extensive coverage of all PostgreSQL features,syntax and bundled utilities.
About pgDash
pgDash is a modern, in-depth monitoring solution designedspecifically for PostgreSQL deployments. pgDash shows you information andmetrics about every aspect of your PostgreSQL database server, collected usingthe open-source tool pgmetrics.
pgDash provides core reporting and visualization functionality, includingcollecting and displaying PostgreSQL information and providing time-seriesgraphs, detailed reports, diagnostics, alerting, teams and more.Checkout the features here or signup todayfor a free trial.
