PostgreSQL commands to remember

List of commands to remember using postgres DB managment system.

Login, Create user and password

# login to psql client
psql postgres # OR
psql -U postgres
create database mydb; # create db
create user abhilash with SUPERUSER CREATEDB CREATEROLE encrypted password 'abhilashPass!'; 
grant all privileges on database mydb to myuser; # add privileges

Connect to DB, List tables and users, functions, views, schema

\l # lists all the databases
\c dbname # connect to db
\dt # show tables
\d table_name # Describe a table
\dn # List available schema
\df #  List available functions
\dS [your_table_name] # List triggers
\dv # List available views
\du # lists all user accounts and roles 
\du+ # is the extended version which shows even more information.

Show history, save to file, edit using editor, execution time, help

SELECT version(); # version of psql
\g  # Execute the previous command
\s # Command history
\s filename # save Command history to a file
\i filename # Execute psql commands from a file
\? # help on psql commands
\h ALTER TABLE # To get help on specific PostgreSQL statement
\timing #  Turn on/off query execution time
\e # Edit command in your own editor
\e [function_name] # It is more useful when you edit a function in the editor. Do \df for functions
\o [file_name] # send all next query results to file
    \o out.txt
    \dt 
    \o # switch
    \dt

Change output, Quit psql

# Switch output options
\a command switches from aligned to non-aligned column output.
\H command formats the output to HTML format.
\q # quit psql

Reference: https://www.postgresqltutorial.com/postgresql-administration/psql-commands/

Setup Nginx, SSL , Firewall | Moving micro-services into AWS EC2 instance – Part 4

Install Nginx proxy server. Nginx also act like a load-balacer which is helpful for the balancing of network traffic.

sudo apt-get update
sudo apt-get install nginx

Commands to stop, start, restart, check status

sudo systemctl stop nginx
sudo systemctl start nginx
sudo systemctl restart nginx

# after making configuration changes
sudo systemctl reload nginx
sudo systemctl disable nginx
sudo systemctl enable nginx

Install SSL – Letsencrypt

Install packages needed for ssl

sudo add-apt-repository ppa:certbot/certbot
sudo apt-get update
sudo apt-get install python-certbot-nginx

Install the SSL Certificate:

certbot -d '*.domain.com' -d domain.com --manual --preferred-challenges dns certonly

Your certificate and chain have been saved at:
   /etc/letsencrypt/live/domain.com/fullchain.pem

Your key file has been saved at:
   /etc/letsencrypt/live/domain.com/privkey.pem
SSL certificate auto renewal

Let’s Encrypt’s certificates are valid for 90 days. To automatically renew the certificates before they expire, the certbot package creates a cronjob which will run twice a day and will automatically renew any certificate 30 days before its expiration.

Since we are using the certbot webroot plug-in once the certificate is renewed we also have to reload the nginx service. To do so append –renew-hook “systemctl reload nginx” to the /etc/cron.d/certbot file so as it looks like this:

/etc/cron.d/certbot
0 */12 * * * root test -x /usr/bin/certbot -a \! -d /run/systemd/system && perl -e 'sleep int(rand(3600))' && certbot -q renew --renew-hook "systemctl reload nginx"

To test the renewal process, use the certbot –dry-run switch:

sudo certbot renew --dry-run

Renew your EXPIRED certificate this way:

sudo certbot --force-renewal -d '*.domain.com' -d domain.com --manual --preferred-challenges dns certonly

Are you OK with your IP being logged?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
(Y)es/(N)o: Y

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Please deploy a DNS TXT record under the name
_acme-challenge.<domain>.com with the following value:

O3bpxxxxxxxxxxxxxxxxxxxxxxxxxxY4TnNo

Before continuing, verify the record is deployed.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Press Enter to Continue

You need to update the DNS txt record for _acme-challenge.<domain>.com

sudo systemctl restart nginx # restart nginx to take effect

Configure the Firewall

Next, we’ll update our firewall to allow HTTPS traffic.

Check firewall status in the system. If it is inactive enable firewall.

sudo ufw status # check status

# enable firewall
sudo ufw enable
sudo ufw allow ssh
sudo ufw allow OpenSSH

Enable particular ports where your micro-services are running. Example:

sudo ufw allow 4031/tcp # Authentication service
sudo ufw allow 4131/tcp # File service
sudo ufw allow 4232/tcp # Search service

You can delete the ‘Authentication service’ firewall rule by:

sudo ufw delete allow 4031/tcp