How to Set Up a PostgreSQL Database in PGAdmin: A Step-by-Step Guide

In this tutorial, we’ll walk through the process of setting up a PostgreSQL database using the PGAdmin application. We’ll cover everything from downloading and installing PGAdmin to creating a database, user, table, and finally verifying the table with a query.

1. Download & Install the PGAdmin App

Steps:

• Visit the PGAdmin Website:
  Navigate to PGAdmin Downloads using your web browser.

• Download the Installer:
  Choose the appropriate installer for your operating system (Windows, macOS, or Linux) and download the package.

• Install PGAdmin:
  Run the downloaded installer and follow the on-screen instructions.
  Note: During installation, you may be asked to set a master password. Remember this password as you’ll need it to access PGAdmin later.

2. Create a Database (my_test_db)

Steps:

• Launch PGAdmin:
  Open the PGAdmin application from your system’s application menu.

• Connect to Your Server:
  When PGAdmin starts, it will prompt you for your master password. Enter it to view your PostgreSQL server in the left-hand browser panel.

• Navigate to the Databases Node:
  In the browser tree on the left, locate and expand your server node (usually named after your machine or “PostgreSQL”).

• Create a New Database:
  • Right-click on the Databases folder.
  • Select Create > Database…
  • In the dialog box that appears, enter the following:
  • Database: my_test_db
  • Owner: (usually defaults to the primary user created during installation)
  • Click Save to create your new database.

3. Create a User (my_app_user) with Password and Grant Privileges

Steps:

• Navigate to Login/Group Roles:
  In the left-hand browser panel, find and expand the server node, then locate Login/Group Roles.

• Create a New Role:
  • Right-click on Login/Group Roles and select Create > Login/Group Role…
  • In the General tab, set:
  • Role Name: my_app_user
  • Password: Enter a secure password and confirm it.
  • In the Definition tab, you can set additional parameters (optional).
  • In the Privileges tab, check the boxes for privileges such as Can login and any others as required.
• Grant Database Access Privileges:
  • Next, connect to your database (my_test_db) by right-clicking it in the browser panel.
  • Select Properties > Privileges tab.
  • Click Add/Change and select my_app_user from the dropdown.
  • Grant the necessary privileges (e.g., CONNECT, TEMPORARY, etc.) to allow this user to access the database from external sources.
  • Click Save to apply the changes.

Note: For remote access, ensure your PostgreSQL server’s configuration files (e.g., pg_hba.conf) are set to allow connections from outside hosts.

4. Create a Table (Users) Using an SQL Query in PGAdmin

Steps:

• Open the Query Tool:
  • In PGAdmin, right-click on your database (my_test_db) and select Query Tool.
  • The Query Tool will open in a new tab, allowing you to execute SQL commands.
• Create the “Users” Table:
  • In the Query Tool, enter the following SQL command:

  CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL
  );
  

  • Click the Execute/Refresh button (usually a lightning bolt icon) to run the query.
  • You should see a confirmation message in the output panel that the table has been created successfully.

5. Verify the Newly Created Table

Steps:

• Open the Query Tool Again (if not already open):
  • Ensure you are connected to the my_test_db database.
• Run a SELECT Query:
  • Enter the following SQL command to verify the table:

  SELECT * FROM Users;
  

  • Click the Execute/Refresh button to run the query.
  • The output panel should display the column headers (id, email, name) with no rows (since the table is empty).
• Verify via Browser Panel:
  • You can also expand the my_test_db node in the browser panel.
  • Under the Schemas > public > Tables section, locate the Users table to confirm it appears in the database structure.

Conclusion

You have successfully set up a PostgreSQL database using PGAdmin by:
1. Downloading and installing PGAdmin.
2. Creating a database named my_test_db.
3. Creating a user (my_app_user), setting a password, and granting privileges.
4. Creating a table named Users with columns for id, email, and name.
5. Verifying the table with a SELECT query.

This guide provides a solid foundation for managing your PostgreSQL environment using PGAdmin. If you encounter any issues or have questions, feel free to leave a comment or reach out via LinkedIn.

Happy developing!