Chapter 1: First Steps

PostgreSQL is a feature-rich, general-purpose database-management system. It's a complex piece of software, but every journey begins with the first step.

We'll start with your first connection. Many people fall at the first hurdle, so we'll try not to skip past that too swiftly. We'll quickly move on to enabling remote users, and from there, we will move on to getting access through GUI administration tools.

We will also introduce the psql query tool, which is the tool used to load our sample database, as well as many other examples in the book.

For additional help, we've included a few useful recipes that you may need for reference.

In this chapter, we will cover the following recipes:

• Introducing PostgreSQL
• How to get PostgreSQL
• Connecting to the PostgreSQL server
• Enabling access for network/remote users
• Using the pgAdmin GUI tool
• Using the OmniDB GUI tool
• Using the psql query...

Introducing PostgreSQL 14

PostgreSQL is an advanced SQL database server, available on a wide range of platforms. One of the clearest benefits of PostgreSQL is that it is open source, meaning that you have a very permissive license to install, use, and distribute PostgreSQL, without paying anyone any fees or royalties. On top of that, PostgreSQL is known as a database that stays up for long periods and requires little or no maintenance, in most cases. Overall, PostgreSQL provides a very low total cost of ownership.

PostgreSQL is also known for its huge range of advanced features, developed over the course of more than 30 years of continuous development and enhancement. Originally developed by the Database Research Group at the University of California, Berkeley, PostgreSQL is now developed and maintained by a huge army of developers and contributors. Many of these contributors have full-time jobs related to PostgreSQL, working as designers, developers, database administrators...

How to get PostgreSQL

PostgreSQL is 100% open source software and is freely available to use, alter, or redistribute in any way you choose. Its license is an approved open source license, very similar to the Berkeley Software Distribution (BSD) license, though only just different enough that it is now known as The PostgreSQL License (TPL). You can see the license here: https://opensource.org/licenses/PostgreSQL.

How to do it...

PostgreSQL is already being used by many different application packages, so you may find it already installed on your servers. Many Linux distributions include PostgreSQL as part of the basic installation or include it with the installation disk.

One thing to be wary of is that the included version of PostgreSQL may not be the latest release. It will typically be the latest major release that was available when that operating system release was published. There is usually no good reason to stick...

Connecting to the PostgreSQL server

How do we access PostgreSQL?

Connecting to the database is the first experience of PostgreSQL for most people, so we want to make it a good one. Let's do it now, and fix any problems we have along the way. Remember that a connection needs to be made secure, so there may be some hoops for us to jump through to ensure that the data we wish to access is secure.

Before we can execute commands against the database, we need to connect to the database server to give us a session.

Sessions are designed to be long-lived, so you connect once, perform many requests, and eventually disconnect. There is a small overhead during the connection. It may become noticeable if you connect and disconnect repeatedly, so you may wish to investigate the use of connection pools. Connection pools allow pre-connected sessions to be quickly served to you when you wish to reconnect.

Getting ready

First, cache your database. If you don...

Enabling access for network/remote users

PostgreSQL comes in a variety of distributions. In many of these, you will note that remote access is initially disabled as a security measure. You can do this quickly as described here, but you really should read the chapter on Security soon.

How to do it…

By default, PostgreSQL gives access to clients who connect using Unix sockets, provided that the database user is the same as the system's username. Here, we'll show you how to enable other connections.

Note

In this recipe, we mention configuration files, which can be located as shown in the Finding the current configuration settings recipe in Chapter 3, Server Configuration.

The steps are as follows:

1. Add or edit this line in your postgresql.conf file:

   listen_addresses = '*'

2. Add the following line as the first line of pg_hba.conf to allow access to all databases...

Using the pgAdmin4 GUI tool

Graphical administration tools are often requested by system administrators. PostgreSQL has a range of tool options. In this book, we'll cover pgAdmin4 and OmniDB.

pgAdmin4 is a client application that sends and receives SQL to and from PostgreSQL, displaying the results for you. The admin client can access many databases servers, allowing you to manage a fleet of servers. The tool works in both standalone app mode and within web browsers.

How to do it…

pgAdmin 4 is usually named just pgAdmin. The 4 at the end has a long history but isn't that important. It is not the release level; pgAdmin 4 replaces the earlier pgAdmin 3.

When you start pgAdmin, you will be prompted to register a new server. 

Give your server a name on the General tab, and then click Connection and fill in the five basic connection parameters, as well as the other information. You should uncheck...

Using the OmniDB GUI tool

Graphical administration tools are often requested by system administrators. PostgreSQL has a range of tool options. In this book, we'll cover pgAdmin4 and OmniDB.

OmniDB is a client application that sends/receives SQL to/from PostgreSQL, displaying the results for you. The admin client can access many databases servers, allowing you to manage a fleet of servers. The tool works in both standalone app mode and within web browsers.

OmniDB is designed to access PostgreSQL, MySQL, MariaDB, and Oracle in one interface, though it makes sure it provides full features for the PostgreSQL database: https://github.com/OmniDB/OmniDB.

OmniDB provides a very responsive interface and is designed with full security in mind. It can be used as a desktop application, and it can also be served using a web server to be accessed by the web browser of your choice.

How to do it…

OmniDB has the standard tree-view browsing interface...

Using the psql query and scripting tool

psql is the query tool supplied as a part of the core distribution of PostgreSQL, so it is available in all environments and works similarly in all of them. This makes it an ideal choice for developing portable applications and techniques.

psql provides features for use as both an interactive query tool and as a scripting tool.

Getting ready

From here on, we will assume that the psql command is enough to allow you access to the PostgreSQL server. This assumes that all your connection parameters are defaults, which may not be true.

Written in full, the connection parameters will be either of these options:

psql -h myhost -p 5432 -d mydb -U myuser 
psql postgresql://myuser@myhost:5432/mydb

The default value for the port (-p) is 5432. By default, mydb and myuser are both identical to the operating system›s username. The default myhost on Windows...

Changing your password securely

If you are using password authentication, then you may wish to change your password from time to time. This can be done from any interface. pgAdmin is a good choice, but here we show to do that from psql.

How to do it…

The most basic method is to use the psql tool. The \password command will prompt you once for a new password and again to confirm. Connect to the psql tool and type the following:

SET password_encryption = 'scram-sha-256'; 
\password

Enter a new password. This causes psql to send a SQL statement to the PostgreSQL server, which contains an already encrypted password string. An example of the SQL statement sent is as follows:

ALTER USER postgres PASSWORD 'SCRAM-SHA-256$4096:H45+UIZiJUcEXrB9SHlv5Q==$I0mc87UotsrnezRKv9Ijqn/zjWMGPVdy1zHPARAGfVs=:nSjwT9LGDmAsMo+GqbmC2X/9LMgowTQBjUQsl45gZzA=';

Make sure you use the SCRAM-SHA-256 encryption, not the older and...

Avoiding hardcoding your password

We can all agree that hardcoding your password is a bad idea. This recipe shows you how to keep your password in a secure password file.

Getting ready

Not all database users need passwords; some databases use other means of authentication. Don't perform this step unless you know you will be using password authentication and you know your password.

First, remove the hardcoded password from where you set it previously. Completely remove the password = xxxx text from the connection string in a program. Otherwise, when you test the password file, the hardcoded setting will override the details you are about to place in the file. Keeping the password hardcoded and in the password file is not any better. Using PGPASSWORD is not recommended either, so remove that also.

If you think someone may have seen the password, change your password before placing it in the secure password file.

How to do it…

...

Using a connection service file

As the number of connection options grows, you may want to consider using a connection service file.

The connection service file allows you to give a single name to a set of connection parameters. This can be accessed centrally to avoid the need for individual users to know the host and port of the database, and it is more resistant to future change.

You can set up a system-wide file as well as individual per-user files. The default file paths for these files are /etc/pg_service.conf and ~/.pg_service.conf respectively.

A system-wide connection file controls service names for all users from a single place, while a per-user file applies only to that particular user. Keep in mind that the per-user file overrides the system-wide file – if a service is defined in both the files, then the definition in the per-user file will prevail.

How to do it…

First, create a file named pg_service.conf with...

Troubleshooting a failed connection

This recipe is all about what you should do when things go wrong.

Bear in mind that 90% of problems are just misunderstandings, and you'll quickly be on track again.

How to do it…

Here, we've made a checklist to be followed if a connection attempt fails:

• Check whether the database name and the username are accurate. You may be requesting a service on one system when the database you require is on another system. Recheck your credentials; ensure that you haven't mixed things up and that you are not using the database name as the username, or vice versa. If you receive an error for too many connections, then you may need to disconnect another session before you can connect or request the administrator to allow further connections.
• Check for explicit rejections. If you receive the pg_hba.conf rejects connection for host... error message, it means that your connection attempt has been explicitly...

PostgreSQL in the cloud

Like many other systems, PostgreSQL is available in the "cloud" as a Database as a Service (DbaaS). These services create and manage databases for you, with high availability and backup included. So its less work, but not zero work, and you still have responsibilities…which you will see later.

Getting ready

We will select EDB's BigAnimal as an example of a PostgreSQL cloud service, since EDB has the largest number of contributors to open source PostgreSQL, over the longest period.

EDB's BigAnimal creates clusters within your own cloud account, allowing you to understand and control the costs you incur when running PostgreSQL. So, the first step is to login into your host cloud account: https://www.biganimal.com/.

How to do it…

Using EDB's BigAnimal as a specific example, navigate through these steps:

1. If you don't have an account, you can sign in using the Free Trial at http://www.biganimal.com...