PostgreSQL cheat sheet is defined as how to use the basic commands of PostgreSQL, we have created our basic cheat sheet of PostgreSQL to take a quick overview of the PostgreSQL database. Copyright show PostgreSQL usage and distribution terms g FILE or; execute query (and send results to file or pipe) h NAME help on syntax of SQL commands,. for all commands q quit psql Query Buffer e FILE edit the query buffer (or file) with external editor. Postgresql Basic Commands Login to postgresql. Psql -U postgres psql -d mydb -U myuser -W psql -h myhost -d mydb -U myuser -W psql -U myuser -h myhost 'dbname=mydb sslmode=require' # ssl connection. Default Admin Login. Sudo-u postgres psql -U postgres sudo-u postgres psql List databases on postgresql server. PostgreSQL CHEAT SHEET DROP VIEW viewname; Delete a view DROP INDEX idxname; Drop an index CREATE VIEW v(c1,c2) AS SELECT c1, c2 FROM t; WITH CASCADED LOCAL CHECK OPTION; Create a new view with check option CREATE RECURSIVE VIEW v AS select-statement -anchor part UNION ALL select-statement; -recursive part. PostgreSQL Cheat Sheet PostgreSQL. Created on: 2019-01-22. Psql Meta-Commands. The following are the Meta-Commands for psql command.
PostgreSQL Commond Line Cheat Sheet
##General Postgres Command
change to postgres user and open psql prompt
>sudo -u postgres psql postgres
password “test”
show postgres versoin
>postgres=# SELECT version();
list databases
>postgres=# l
connect to database
>postgres=# c <databasename>
switch to different database with role <role-name>
>postgres=# c <databasename> <role-name>
list tables in connected database
>postgres=# dt
list columns on table
>postgres=# d <tablename>
Postgresql Commands Cheat Sheet Pdf Download Free
exit console
>postgres=# Ctrl-D or q
##Performance tuning
turn on query timing
>postgres=# timing
analyze query
>postgres=# EXPLAIN ANALYZE SELECT COUNT(*) FROM hm_client_session;
look at locks
fast count estimate rows in a table
>reportingdb_1=# SELECT reltuples::bigint AS estimate FROM pg_class where relname=’hm_client_session’;
estimate
———-
7375553
(1 row)
Time: 0.710 ms
##configdb check
connect to database
>postgres=# c configdb_1
copy query output as CSV to file
>configdb_1=# Copy (SELECT * FROM hm_device WHERE is_connected=false) To ‘/tmp/hm_device.csv’ with CSV HEADER;
Microsoft office 2011 free download. drop database
>postgres=# DROP DATABASE demodb1;
## Backup and restore database
> pg_dump -i-h localhost -p5432-U postgres -F t -b-v-f“~/backups/backup-file.backup” database_name_to_backup
–i, –ignore-version proceed even when server version mismatches. Usefulwhile migrating data between servers. –h localhost, host to connect, can be IP address –p 5432, default port used by PostgreSQL–U postgres, this is default username, make sure user has rights to backup database –F t, -format used for backup, I suggest always using “t” (tar), other options are listed below –b, –blobs include large objects in dump –v, –verbose –f “~/backups/test-database.backup”, file where backup will be stored database_name_to_backup, database name which we want to backup
>pg_restore -i-h localhost -p5432-U postgres -d old_db -v“~/backups/backup-file.backup”
–p, –port=PORT database server port number –i, –ignore-version proceed even when server version mismatches –h, –host=HOSTNAME database server host or socket directory –U, –username=NAME connect as specified database user –W, –password force password prompt (should happen automatically) –d, –dbname=NAME connect to database name –v, –verbose verbose mode
## Privilege and Roles
list roles
>postgres=# du
create role
>postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD ‘password1’ CREATEDB;
create role with multiple privileges
>postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD
>postgres=# ‘password1’ CREATEDB CREATEROLE REPLICATION SUPERUSER;
alter role
>postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;
drop role
Postgresql Shell Cheat Sheet
>postgres=# DROP ROLE demorole1;
Postgres Psql Command
create database
Postgresql Commands Cheat Sheet 2020
>postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING ‘UTF8’;
Postgresql Command Line Cheat Sheet
grant privileges to new user
>postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1; Xpad download.