Versions:

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.

Connections

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

Functions

Edit a function

\ef function_name

Display all functions

\df

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

\df+

Views

List views from current schema

\dv

List views from all schemas

\dv+

Users

Display PostgreSQL database roles

\du

Reset the password for a PostgreSQL database user

\password username

Indexes and extensions

List all indexes from all tables

\di

Show all extensions installed

\dx

Import and export CSV

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

Import

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

Export

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

Backup and restore

note

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!

Keywords

Related Content