Connecting to PostgreSQL: Learning PostgreSQL with Grant

There was a time when most of us worked on a single data platform. Even today, many of us can easily say that we spend the majority of our time on one data platform. However, more of us are moving to manage or develop on, multiple data platforms. I’m right there with you. I have to support and understand many different platforms now. The one I’ve been working with the most is PostgreSQL. Yet, I don’t really feel like I know it very well at all. 

That’s going to change.

I’m going to go on a deeper dive into the PostgreSQL database and bring you along the journey with me. It’s not enough to be able to write a SQL script that works in PostgreSQL. Instead, I want to be able to perform real management, from backups to troubleshooting. Further, I want to develop a database there, with tables, keys, procedures, and all the rest. Finally, I want to be able to monitor and performance tune the queries in PostgreSQL.

That’s the plan.

Now, am I going to go so far as to start writing books on the topic?

No.

However, as I figure things out, I am going to write these blog posts so they can act as a learning guide for others.

If anyone has suggestions, questions, or any feedback at all, please, don’t hesitate to reach out.

Running PostgreSQL

In these wonderful modern times, you really don’t need to install software. Containers and database as a service offerings allow skipping that whole mess. However, if you do want to run PostgreSQL as a service locally, you will need to download PostgreSQL.

Pick the appropriate OS, and then you can download and run the install. Obviously, you can use Chocolatey, HomeBrew, or some other utility for a command line install.

Once you have it installed, you will need to start the service. I’m running Windows 11 at the moment, so I used the following command from a terminal window:

postgres -D ./data

The command means that it’s going to be running in the foreground of that terminal. It’s on the default port, 5432, with the login name ‘postgres’ and whatever password was supplied during the install.

This series of articles uses PostgreSQL on demand, not as a service. That may change down the line, but for now, I’m focusing more on PostgreSQL internal behaviors, not so much about setting it up for a production system.

Using a Docker container to run PostgreSQL is a simple option:

docker pull postgres

That command will get you a PostgreSQL docker image with the latest build, which is, as of this writing, 14.1.1. Running the following will create a container and get things started:

docker run --name PostgreHome -e POSTGRES_PASSWORD=Asecurep@ssw0rd -p 5432:5432 -d postgres

Again, this will run on the default port. If you plan to run multiples of the services or containers, you will need to change the ports.

If all this wasn’t enough, Azure fully supports PostgreSQL database on its excellent data platform. This is configured for you, secure by default, and includes backups as well as a whole host of enhancements. When I’m not running in a container, I’ll probably use this the most for this training.

Finally, AWS offers up two ways to run PostgreSQL. You can use AWS RDS or AWS Aurora. Both run a managed instance of PostgreSQL with many additional bells and whistles like from Azure.

By using one of these four different methods, regardless of your operating system, you should be able to get an instance of PostgreSQL up and running.

Connecting to PostgreSQL

With one of these services running, you next have to connect to PostgreSQL with a tool that will let you run queries. There are a whole slew of them out there. I plan to use Azure Data Studio as my principal tool. I’m picking this because I can easily add my scripts into Github source control, connect to PostgreSQL, and run queries, all from a single location and tool. However, PostgreSQL comes with some tools of its own. Let’s look at those first.

psql

psql is a command-line tool that comes with PostgreSQL. (You can also get it along with the pgAdmin tool discussed in the next section if you didn’t install PostgreSQL locally.) To get started with it, assuming PostgreSQL is in your system path, just do the following:

psql -U postgres

The command starts a psql session, and the user name I wish to log in with is ‘postgres’, the default user. This command assumes I’m running locally through either a container or a service using localhost and the default port. If any of that is not valid, you’ll have to change settings. Hitting enter will bring you to a password prompt. Use the password you used in whichever system you set up above, and you’ll end up at a prompt:

There are many commands you can run within psql. For example, to see the databases use the command:

\list

The output will look something like this:

You can then connect to one of those databases like this:

\connect postgrelearning

You’ll then see the prompt has changed to show the different database you’re in:

From there, you’re using SQL within PostgreSQL. Just remember that you must use the semicolon as a statement terminator to make the query run.

pgAdmin

Another tool you can use is pgAdmin, a browser-based tool for working with your PostgreSQL databases. Like with so much else these days, you can download an executable and install it or grab a container. You’ll have to create an account with a password to use this tool. Connecting is the same as anything else; put in the name or the IP address, port, login, and password.

After connecting, you get something that looks like this:

You get some monitoring and other functionality for administering the server and databases. You also get a full query editor:

You get formatting and some limited code completion. There’s the ability to look at explain plans.

pgAdmin 4 is up to version 6.1. Yeah, I find that confusing too, but that’s what’s going on. It’s a pretty darned complete management tool, even if it’s browser-based.

Azure Data Studio

Azure Data Studio is a multi-platform query tool that currently works with Microsoft SQL Server and PostgreSQL. It’s primarily a development tool for writing queries, although an object explorer lets you look through objects within the database.

To connect to PostgreSQL, you’ll have to install the PostgreSQL extension to Azure Data Studio.

Connections are the same as with the other tools. Fill in the server name or IP, the port if different from the default, user name, and password.

The query editor has good code completion. Running queries looks like this:

Conclusion

I’ve no doubt I’m making some mistakes as I learn this stuff, and I’ll include updates as we go along. This article covered enough to get anyone started within PostgreSQL. From a service, to a container, to a web service, you can run PostgreSQL. Once it’s running, you have multiple tools that can easily connect up to your server or databases. From there, it’s just a question of learning. I’m going to start on the one action I would look to if I were taking on this stuff for a job, backups.

 

The post Connecting to PostgreSQL: Learning PostgreSQL with Grant appeared first on Simple Talk.

from Simple Talk https://ift.tt/3CI025X
via