Postgres Management with the CLI

Apr 25, 2021 · 1 min read

PostgreSQL is an object-relational database management system (ORDBMS). It is widely used as an enterprise database management system. In this article I will show you what commands I use on the postgres CLI to install, manage and run the database for my projects

Installation

$ sudo apt-get install postgresql # latest version
# or
$ sudo apt-get install postgresql-12 # specific version

Run postgres CLI with a particular user

$ sudo su username  # Change user to postgres
$ psql              # Go to the postgres CLI

Or do it one go

$ psql -U username

$ psql -U postgres

Create a user

$ psql -U postgres -c "create user john createdb password 'john_snow'"

Export a database in SQL form

$ pg_dump -U postgres db_name > export_file.sql

Import database

$ sudo su postgres
$ psql teq-db < export_file.sql

Create and delete database

postgres=# drop database "db_name";

postgres=# create database "db_name";