Psql basic commands

Psql is the terminal-based front end to PostgreSQL, and is the primary tool used to communicate with your TimescaleDB instances. Below is a refresher on some essential psql commands that you may come across in our documentation, and could find useful as you explore PostgreSQL and TimescaleDB.

For an in-depth breakdown of all commands, visit psql's documentation.


Connect to a PostgreSQL/TimescaleDB server via psql (using parameter flags):

psql -h host_name -p port -U username -W -d database_name

Connect to a PostgreSQL/TimescaleDB server via psql (using a service URL):

psql "your_service_URL"

Show your psql version

psql --version


Edit a function

\ef function_name

Display all functions


Display all functions with more details (including owner, source code, and description)



List views from current schema


List views from all schemas



Display PostgreSQL database roles


Reset the password for a PostgreSQL database user

\password username

Indexes and extensions

List all indexes from all tables


Show all extensions installed


Import and export CSV

The commands in this section can be used to import and export CSV files from your PostgreSQL or TimescaleDB database.


\copy table_name FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER


\copy (SELECT * FROM table_name) TO 'output_file.csv' WITH CSV HEADER

Backup and restore


The commands in this section use the pg_dump and pg_dumpall tools.

Create a backup for a specific database in plan-text SQL script file (with default backup options)

pg_dump database_name > database_name.bak.sql

Creates a backup for a specific database in plan-text SQL script file (with customized backup options)

pg_dump -c -C -F p -f database_name.bak.sql database_name

Run pg_dump on a client computer to back up data on a remote Postgres server

pg_dump -h remote_host -p port -U user -f database_name.bak mydb

Backup all databases along with database roles and cluster-wide information

pg_dumpall > alldb.bak.sql

Restore the database plain-text backup generated by pg_dump or pg_dumpall

psql -U username -f filename.sql

Restore the database custom-archive backup (.bak)

pg_restore -d database_name /path/to/your/file/database_name.bak -c -U database_user

Found an issue on this page?

Report an issue!


Related Content