A brief overview & tutorial to connect a PostgreSQL database(Amazon RDS)
What is pgAdmin?
It’s a very popular open source platform fully dedicated to PostgreSQL and has a graphical user interface administration tools to manage your relational databases. Some features include a query tool for SQL statements and importing/exporting csv files.
- Download pgAdmin & PostgreSQL
- pgAdmin Overview
- Connect an Existing Database(AWS)
Note: To my knowledge the tutorial will work with macOS and Windows
Notes: I’m using macOS, version # will change, and the page layout could change
To use pgAdmin, you will need to download it on your system. Pick pgAdmin 4 for your OS.
After selecting your OS, a download page showing different version releases in descending order will appear. Choose the latest version and it will take you to the file browser page. Then click the largest file to download and follow the installation instructions.
For PostgreSQL, you can download it here. Choose the latest version for your machine.
Notes: When installing PostgreSQL you don’t need to change any of the default settings. If you forget your password, you won’t have access to your PostgreSQL server anymore.
PgAdmin will use your preferred web browser to display a graphical user interface. You don’t need internet to view local servers. It will prompt you for a master password every time you open pgAdmin to get access. After getting access click Servers(1) on the left side to open up your PostgreSQL 12 server. If you don’t see a server, try restarting pgAdmin.
Open up your database to get a tree view menu according to the picture below. You will see a database named postgres. Selecting the database will bring up an activity dashboard to view traffic information.
You have access to useful tools by right-clicking on any object in the tree view menu. We can create a new database, schema, and tables. As well as viewing individual table data and customizing an existing table.
Let’s create a table by right clicking on Tables and click Table…
Give your table a name and then click Columns
Click the + symbol to add columns
Name your column, select the data type, and give a length if needed. Then click Save
Now that you have created a table, view it under the Tables object. Right click and refresh if it didn’t update.
Query Tool Example:
Note: I’m showing you my database with data for demonstration purposes
When an object is selected under the database tree view menu, you can click the Tools tab and click Query Tool. Query tool brings up an editor to execute SQL statements.
You can also right click the database and click Query Tool …
A tab called query editor will open up on the right. You can write SQL statements and click the play button to execute. Your results will show below the editor on the Data Output tab.
For more documentation on pgAdmin click here
Connect an Existing Database
Note: We will be using Amazon RDS PostgreSQL as an example and if you need help setting one up, click here
Your database will need public access to connect with pgAdmin. Depending on your database configuration this example might not work.
Database Credentials Needed: Host Name, Port, Username, Password
Right-click Servers and click on Server…
Name your server with the same AWS database name, then click Connection
Fill in your Host name/address, Port, Username and Password. Click Save
NOTE: If you don’t want to put in your database password every time you open the server click the Save password? box
Now you should have your database connected with pgAdmin. Try using the query tool to view your data with SQL statements. :)
The pgAdmin Development Team are pleased to announce pgAdmin 4 version 4.18. This release of pgAdmin 4 includes over 23…
PostgreSQL: The world's most advanced open source database
2020-02-13 - PostgreSQL 12.2, 11.7, 10.12, 9.6.17, 9.5.21, and 9.4.26 Released! The PostgreSQL Global Development Group…