Setting up PostgreSQL + timescaledb (extension)

This blog capture my efforts and lessons learned during the migration of stock trading data from sqlite3 → Postgres + timescaledb extension

Installation and setup

update source repos

echo “deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main” | sudo tee /etc/apt/sources.list.d/pgdg.list
wget — quiet -O — https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update

Install postgreSQL and addon timescaledb

sudo apt install timescaledb-2-postgresql-13
tuneup the timescaledb
sudo timescaledb-tune 

Basic command of PostgreSQL

logging into the postgreSQL using postgres user. Follow this blog

bottomline is the default user postgres does not comes with password and you just have to login from your machine sudo password to get the database shell and change its password

sudo -u postgres psql
postgres=# ALTER USER postgres PASSWORD 'NewPassword';
ALTER ROLE

OR

\password postgres

Create a database and giving access to a another db user

This blog is helpful.

sudo -u postgres psql
postgres=# create database mydb;
postgres=# create user myuser with encrypted password ‘mypass’;
postgres=# grant all privileges on database mydb to myuser;

List all databases

\l+

Change database

postgre=# \c <database_name> 

eg \c nyc_data

list all database users

postgre=# \du+

list all tables in a database along with size

postgre=# \dt+

List table schema (including constraints)

my_database=>\d+ table_name

More advanced command

ALTER TABLE snp_500_tickers ADD PRIMARY KEY (Symbol);
DELETE FROM <TABLE_NAME>
SELECT table_name, column_name , data_type   from information_schema.columns WHERE table_name = 'stock_all_tradable';
DROP SEQUENCE <sequence_name> ;
CREATE SEQUENCE stock_id_sequence START WITH 9495;
ALTER TABLE stock_all_tradable ALTER COLUMN id SET DEFAULT nextval('stock_id_sequence');
\pset # list the current configuration of display
\pset tuple_only # make sure to set it off
EXPLAIN PLAN <QUERY>

Attach timescale db extension to database

After creating a database if you want to use the timescaledb extension you need to use. Go inside the particular database you want to use

\c my_database
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Configure the postgres admin UI (pgadmin4)

sudo apt-get install curl
sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
sudo apt install pgadmin4
#Launch the webserver and visit the admin page
sudo /usr/pgadmin4/bin/setup-web.sh
http://127.0.0.1/pgadmin4

Migrating data using pgloader

Before migration: Make sure your table from sqlite3 have correct datatypes. Also make sure the database you want to migrate the tables to should exist in postgres. Also lot of time i had migration error because of datatype conversion not possible.

pgloader pgloader_script

pgloader_script will look like

load database 
    from sqlite:///home/dpant/stock_trade/dbf/daily_price.db
    into postgresql:///minute_price_data_db
with include drop, create tables, create indexes, reset sequences      
set work_mem to '16MB', maintenance_work_mem to '512 MB';
PS: This blog post will continuously update for next few days