# Postgres commands you should be using login to postgresql user ```bash su - postgres ``` To enter in postgresql:- ```bash su - postgres psql ``` To create database ```bash create database ; ``` Create user with password ```bash create user with encrypted password 'password'; ``` To give permission to user with database. ```bash grant all privileges on database to ; ``` To give superuser permission ```bash ALTER USER username WITH SUPERUSER; ``` To login remotely from others place ```bash psql -U username -h localhost -p 5432 dbname ``` To dump from remote server ```bash pg_dump -U -h -p 5432 > databasename.sql ``` To dump postgres database ```bash pg_dump databasename > dbname.sql ``` To restore db dump ```bash psql databasename < path-dbname.sql ``` database dump of single table ```bash pg_dump -d -t > file.sql ``` and to restore single table ```bash psql -h localhost -U postgres -p 5432 database < path/to/store/name.sql ``` Postgres schema only dump ```bash pg_dump -U postgres -s postgres > exportFile.sql ``` Postgres schema only with specific table ```bash pg_dump -s -t tablename databasename > dump.sql ``` Login directly to postgres ```bash sudo -u postgres psql ``` To drop database with force ```sql DROP DATABASE WITH (FORCE); ``` To list database

\c dbname	Switch connection to a new database
\l	List available databases
\dt	List available tables
\d table_name	Describe a table such as a column, type, modifiers of columns, etc.
\dn	List all schemes of the currently connected database
\df	List available functions in the current database
\dv	List available views in the current database
\du	List all users and their assign roles
SELECT version();	Retrieve the current version of PostgreSQL server
\g	Execute the last command again
\s	Display command history
\s filename	Save the command history to a file
\i filename	Execute psql commands from a file
\?	Know all available psql commands
\h	Get help	Eg:to get detailed information on ALTER TABLE statement use the \h ALTER TABLE
\e	Edit command in your own editor
\a	Switch from aligned to non-aligned column output
\H	Switch the output to HTML format
\q	Exit psql shell

Creating Extensions ```sql CREATE EXTENSION ; ``` Make sure you switch to specified databases and enable extension and install necessary packages before enabling You can also check below options in Postgres ```text Usage: pg_dump [OPTION]... [DBNAME] General options: -f, --file=FILENAME output file or directory name -F, --format=c|d|t|p output file format (custom, directory, tar, plain text (default)) -j, --jobs=NUM use this many parallel jobs to dump -v, --verbose verbose mode -V, --version output version information, then exit -Z, --compress=0-9 compression level for compressed formats --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock -?, --help show this help, then exit Options controlling the output content: -a, --data-only dump only the data, not the schema -b, --blobs include large objects in dump -c, --clean clean (drop) database objects before recreating -C, --create include commands to create database in dump -E, --encoding=ENCODING dump the data in encoding ENCODING -n, --schema=SCHEMA dump the named schema(s) only -N, --exclude-schema=SCHEMA do NOT dump the named schema(s) -o, --oids include OIDs in dump -O, --no-owner skip restoration of object ownership in plain-text format -s, --schema-only dump only the schema, no data -S, --superuser=NAME superuser user name to use in plain-text format -t, --table=TABLE dump the named table(s) only -T, --exclude-table=TABLE do NOT dump the named table(s) -x, --no-privileges do not dump privileges (grant/revoke) --binary-upgrade for use by upgrade utilities only --column-inserts dump data as INSERT commands with column names --disable-dollar-quoting disable dollar quoting, use SQL standard quoting --disable-triggers disable triggers during data-only restore --enable-row-security enable row security (dump only content user has access to) --exclude-table-data=TABLE do NOT dump data for the named table(s) --if-exists use IF EXISTS when dropping objects --inserts dump data as INSERT commands, rather than COPY --no-security-labels do not dump security label assignments --no-synchronized-snapshots do not use synchronized snapshots in parallel jobs --no-tablespaces do not dump tablespace assignments --no-unlogged-table-data do not dump unlogged table data --quote-all-identifiers quote all identifiers, even if not key words --section=SECTION dump named section (pre-data, data, or post-data) --serializable-deferrable wait until the dump can run without anomalies --snapshot=SNAPSHOT use given snapshot for the dump --strict-names require table and/or schema include patterns to match at least one entity each --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership Connection options: -d, --dbname=DBNAME database to dump -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port number -U, --username=NAME connect as specified database user -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) --role=ROLENAME do SET ROLE before dump If no database name is supplied, then the PGDATABASE environment variable value is used. ```