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):
Show your psql version
Edit a function
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
List all indexes from all tables
Show all extensions installed
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
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!