Note: at the time this tutorial was first drafted, the employed PostgreSQL version was the 14.0 .
In most cases, the following is still applicable to more recent releases.
SQL statements hereby apply in most cases to any SQL-supported database with minor differences.

Upon fresh install, you'll need to log in as postgres to run the following statements. You can switch to the user postgres via: :
sudo -i -u postgres or su - postgres

by the Ctrl+D keystroke you'll go back to your prior log-in.

createuser username

createdb dbname

psql -h host -U usr -d myDB -a -f createtable.sql

First, enter psql
sudo -u postgres psql

by the Ctrl+D keystroke you can exit psql.

\l

\du+

\c dbname

First, enter psql
sudo -u postgres psql

ALTER USER username WITH ENCRYPTED PASSWORD 'password';

First access the desired db
\c dbname
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schemaname TO user;
the default schema being public

SELECT usename AS role,  
 CASE  
     WHEN usesuper AND usecreatedb THEN  
	   CAST('superuser, create database' AS pg_catalog.text)  
     WHEN usesuper THEN 
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' AS pg_catalog.text)
     ELSE 
	    CAST('' AS pg_catalog.text)
 END attributes
FROM pg_catalog.pg_user
ORDER BY role desc;

and show them by descending size

SELECT table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
FROM information_schema.tables
ORDER by 3 desc;

pg_dump -t table_to_copy source_db | psql target_db

pg_dump -U postgres -h <hostname> -vbCO -t <table names pattern> -f <yourdumpfilepath.sql> postgres

First check whether your PGDATA var is set: echo $PGDATA.
If not, cat /etc/postgresql/<postgresql version>/main/postgresql.conf | grep data_directory.
By default, it should be /var/lib/postgresql/<postgresql version>/main.
In which case: mkdir ~/mypostgrebackup && cp -r /var/lib/postgresql/<postgresql version>/main/* ~/mypostgrebackup/

psql -U postgres -d postgres -h <host IP> -P format=unaligned -P tuples_only -P fieldsep=\, -c "SELECT * FROM <tablename>" > mysqldump.csv
 -c \
psql -U postgres -d postgres -h <host IP> -P format=unaligned -P tuples_only -P fieldsep=\, -c "COPY table FROM STDIN WITH DELIMITER ',' CSV" > mysqldump2.csv

This mostly refers to the use-case where your dump resides somewhere else than the db.
In here, we assume a CREATE statement for table already ran. We assume as well, that the delimiter of choice during the dump was the semicolon and that the table headers were included.

psql -p <port> -U <username> -h <host IP> -d <dbname> -c \
"\copy <tablename> FROM '<dump file path>' WITH CSV DELIMITER ';' HEADER;"

if you want to skip some columns:
\copy (select col1,col2 from <tablename>) FROM '<dump file path>' WITH CSV DELIMITER ';' HEADER;
that assumes you have some DEFAULT replacements for columns you are skipping; another use-case is the target table not including some source table columns, altogether. Thereby, (select * from) can be used, or, if specified, the columns must all belong to the target table too.

First, enter psql
sudo -u postgres psql

COPY tablename TO '/path/to/records.csv' WITH CSV DELIMITER ';' HEADER;

If you haven't yet, you must init the table structure first.
Let's say that the tablename populated table resides on db sourcedb:
pg_dump sourcedb -t 'schema-name.tablename' --schema-only
Now you can run the create statement into your target db. Next step is to psql into your target db and run:

COPY (SELECT * FROM tablename) FROM '/tmp/test.csv' WITH CSV DELIMITER ';' HEADER;

note: it might well be that your FROM path refers to a machine other then the one the target db resides on (e.g., a docker container, a remote server). In this case you might want to follow this instead.

SELECT conrelid::regclass AS table,  
       conname AS primary_key,
       pg_get_constraintdef(oid) AS constraint_definition
FROM   pg_constraint
WHERE  contype = 'p'
AND    connamespace = 'public'::regnamespace
ORDER  BY conrelid::regclass::text, contype DESC;

ALTER TABLE tablename ADD PRIMARY KEY ("column1","column2");

CREATE TABLE top_performers
AS (SELECT *
    FROM performers
    WHERE performance > 1000);

Beware of tables' name changes. If existing queries, views, user-defined functions, stored procedures, or programs reference that table, the name change will nullify those objects.

ALTER TABLE william RENAME TO william_the_II;

If you don't find yourself in the Database you want to operate, type and enter \q to go back to the main shell, then log into the desired db using
psql -d dbname and finally
DROP TABLE tablename;

Create an exact copy of the database
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;