{"id":92885,"date":"2021-11-23T17:52:50","date_gmt":"2021-11-23T17:52:50","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92885"},"modified":"2024-04-16T18:59:18","modified_gmt":"2024-04-16T18:59:18","slug":"connecting-postgresql-learning-postgresql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/featured\/connecting-postgresql-learning-postgresql\/","title":{"rendered":"Connecting to PostgreSQL: Learning PostgreSQL with Grant"},"content":{"rendered":"

This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n

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\u2019m right there with you. I have to support and understand many different platforms now. The one I\u2019ve been working with the most is PostgreSQL. Yet, I don\u2019t really feel like I know it very well at all.\u00a0<\/p>\n

That\u2019s going to change.<\/p>\n

I\u2019m going to go on a deeper dive into the PostgreSQL database and bring you along the journey with me. It\u2019s 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.<\/p>\n

That\u2019s the plan.<\/p>\n

Now, am I going to go so far as to start writing books on the topic?<\/p>\n

No.<\/p>\n

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

If anyone has suggestions, questions, or any feedback at all, please, don\u2019t hesitate to reach out.<\/p>\n

Running PostgreSQL<\/h2>\n

In these wonderful modern times, you really don\u2019t 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<\/a>.<\/p>\n

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

Once you have it installed, you will need to start the service. I\u2019m running Windows 11 at the moment, so I used the following command from a terminal window:<\/p>\n

postgres -D .\/data<\/pre>\n
The command means that it\u2019s going to be running in the foreground of that terminal. It\u2019s on the default port, 5432, with the login name \u2018postgres\u2019 and whatever password was supplied during the install.<\/p>\n
This series of articles uses PostgreSQL on demand, not as a service. That may change down the line, but for now, I\u2019m focusing more on PostgreSQL internal behaviors, not so much about setting it up for a production system.<\/p>\n
Using a Docker container to run PostgreSQL is a simple option:<\/p>\n
docker pull postgres<\/pre>\n
That command will get you a PostgreSQL docker image<\/a> with the latest build, which is, as of this writing, 14.1.1. Running the following will create a container and get things started:<\/p>\n
docker run --name PostgreHome -e POSTGRES_PASSWORD=Asecurep@ssw0rd -p 5432:5432 -d postgres<\/pre>\n
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.<\/p>\n
If all this wasn\u2019t enough, Azure fully supports PostgreSQL database<\/a> 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\u2019m not running in a container, I\u2019ll probably use this the most for this training.<\/p>\n
Finally, AWS offers up two ways to run PostgreSQL. You can use AWS RDS<\/a> or AWS Aurora<\/a>. Both run a managed instance of PostgreSQL with many additional bells and whistles like from Azure.<\/p>\n
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.<\/p>\n
Connecting to PostgreSQL<\/h2>\n
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\u2019m 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\u2019s look at those first.<\/p>\n
psql<\/h2>\n
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\u2019t install PostgreSQL locally.) To get started with it, assuming PostgreSQL is in your system path, just do the following:<\/p>\n
psql -U postgres<\/pre>\n
The command starts a psql session, and the user name I wish to log in with is \u2018postgres\u2019, the default user. This command assumes I\u2019m running locally through either a container or a service using localhost and the default port. If any of that is not valid, you\u2019ll have to change settings<\/a>. Hitting enter will bring you to a password prompt. Use the password you used in whichever system you set up above, and you\u2019ll end up at a prompt:<\/p>\n
\"An<\/p>\n
There are many commands you can run within psql. For example, to see the databases use the command:<\/p>\n
\\list<\/pre>\n
The output will look something like this:<\/p>\n
\"List<\/p>\n
You can then connect to one of those databases like this:<\/p>\n
\\connect postgrelearning<\/pre>\n
You\u2019ll then see the prompt has changed to show the different database you\u2019re in:<\/p>\n
\"Image<\/p>\n
From there, you\u2019re using SQL within PostgreSQL. Just remember that you must use the semicolon as a statement terminator to make the query run.<\/p>\n
pgAdmin<\/h2>\n
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<\/a> and install it or grab a container. You\u2019ll 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.<\/p>\n
\"Image<\/p>\n
After connecting, you get something that looks like this:<\/p>\n
\"Image<\/p>\n
You get some monitoring and other functionality for administering the server and databases. You also get a full query editor:<\/p>\n
\"Image<\/p>\n
You get formatting and some limited code completion. There\u2019s the ability to look at explain plans.<\/p>\n
pgAdmin 4 is up to version 6.1. Yeah, I find that confusing too, but that\u2019s what\u2019s going on. It\u2019s a pretty darned complete management tool, even if it\u2019s browser-based.<\/p>\n
Azure Data Studio<\/h2>\n
Azure Data Studio is a multi-platform query tool that currently works with Microsoft SQL Server and PostgreSQL. It\u2019s primarily a development tool for writing queries, although an object explorer lets you look through objects within the database.<\/p>\n
To connect to PostgreSQL, you\u2019ll have to install the PostgreSQL extension to Azure Data Studio.<\/p>\n
\"Image<\/p>\n
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.<\/p>\n
\"The<\/p>\n
The query editor has good code completion. Running queries looks like this:<\/p>\n
\"Image<\/p>\n
Conclusion<\/h2>\n
I\u2019ve no doubt I\u2019m making some mistakes as I learn this stuff, and I\u2019ll 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\u2019s running, you have multiple tools that can easily connect up to your server or databases. From there, it\u2019s just a question of learning. I\u2019m going to start on the one action I would look to if I were taking on this stuff for a job, backups.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Database professionals often work with more than one database platform. Grant Fritchey explains setting up and connecting to PostgreSQL database.…<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143534],"tags":[95506,158977,159066],"coauthors":[6785],"class_list":["post-92885","post","type-post","status-publish","format-standard","hentry","category-featured","category-postgresql","tag-automate","tag-learningpostgresqlwithgrant","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92885","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92885"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92885\/revisions"}],"predecessor-version":[{"id":104382,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92885\/revisions\/104382"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92885"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92885"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92885"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92885"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}