PostgreSQL Database Administration Tools

PostgreSQL Database Administration Tools

Now, let’s talk about Database administration tools.

• Database administration tools are tools that provide an interface for automating database tasks like creating, modifying and deleting databases, setting roles and privileges, extracting data from tables, and any other task that can be run on a database.
• There are a number of free and paid tools used for PostgreSQL Database Administration. Some of the more popular ones include:
  • DBeaver (our focus for this course)
  • pgAdmin
  • Navicat
  • DataGrip
  • HeidiSQL
  • TablePlus
  • Beekeeper Studio
  • OmniDB

Like I stated earlier, the tool we’re using for database administration in this course is DBeaver. DBeaver is a database administration tool for working with all possible SQL, NoSQL, and cloud data sources, not just Postgres. DBeaver has both the Pro version (which is paid) and the community version (which is free). For this course, we’ll be using the community version.

• To get started, let’s first install PostgreSQL
  • PostgreSQL: Downloads*
  • – Switch to PostgreSQL installation screen.
    • Add psql to $PATH: https://postgresapp.com/documentation/cli-tools.html*
    • If experiencing any issues: https://postgresapp.com/documentation/troubleshooting.html*
  • For Windows users:
    • Select the Windows icon
    • Click on “Download the installer”

• Create a new postgres user:
  • Open up your terminal and run: sudo -u postgres psql
  • At the prompt, type your root password and hit the “Return” key
  • Type the default “postgres” password
  • The terminal session changes to postgres=#, indicating a successful connection to the Postgres shell.
    • For Windows users, if you followed the instructions in the link from the previous video, you should be able to access the Postgres shell by now.
  • Execute the following statement to create a user called “altschool_db_user” (feel free to change the password to something of your choice, but make sure it’s something you remember easily):
    • CREATE USER altschool_db_user WITH ENCRYPTED PASSWORD ‘altschool_db_pass’;
    • This creates a “altschool_db_user” user with an encrypted password “altschool_db_pass”.

• Create a new Postgres database:
  • CREATE DATABASE altschool_db;
  • GRANT ALL PRIVILEGES ON DATABASE altschool_db TO altschool_db_user;
  • Type exit and hit the “return” or “enter” key.