Skip to content

PostgreSQL - General Notes

Check if postgres server is running

To check if postgres service is running, run: - pg_isready or, - sudo service postgresql status

Start/stop postgres server

  • sudo service postgresql start
  • sudo service postgresql stop

Common Commands

\l list databases

\q quick postgres

\d show all tables of current database

\c <db> switch databases

\du show users/roles and attributes

Misc

  • To insert sql commands from a local file, run the following commands from inside psql: \i <path_to_file>

  • In order to connect pgAdmin to the local database, log in as the superuser by running:

    • sudo -u postgres psql postgres
    • then create a password for the postgres superuser by running: ALTER USER postgres WITH password 'postgres';
    • This creates a password. pgAdmin doesn't allow connecting to a server (localhost) without a password.
      • the postgres superuser doesn't have a password by default.

Connect pgAdmin to postgres

  • Launch the pgAdmin application, right-click on the server and choose Create > server...
  • In the General tab, give the server a name (i.e. "local_server")
  • In the Connection tab, write "localhost" for the Host name/address and your username for the Username field. If you are unsure what your username is, log into psql (see step 3), and run \du which will give you a list of users and their roles.