anilktechie

1. Change your database RDS instance security group to allow your machine to access it.
   • Add your ip to the security group to acces the instance via Postgres.
2. Make a copy of the database using pg_dump
   • $ pg_dump -h <public dns> -U <my username> -f <name of dump file .sql> <name of my database>
   • you will be asked for postgressql password.
   • a dump file(.sql) will be created
3. Restore that dump file to your local database.
   • but you might need to drop the database and create it first
   • $ psql -U <postgresql username> -d <database name> -f <dump file that you want to restore>

• the database is restored

anilktechie

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

• -E: will describe the underlaying queries of the \ commands (cool for learning!)
• -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

anilktechie

create or replace function create_jsonb_flat_view
    (table_name text, regular_columns text, json_column text)
    returns text language plpgsql as $$
declare
    cols text;
begin
    execute format ($ex$
        select string_agg(format('%2$s->>%%1$L "%%1$s"', key), ', ')
        from (
            select distinct key

anilktechie

from boto3.session import Session
from botocore.client import Config
from botocore.handlers import set_list_objects_encoding_type_url

import boto3

ACCESS_KEY = "xx"
SECRET_KEY = "yy"
boto3.set_stream_logger('')

anilktechie

###
### This gist contains 2 files : settings.json and lambda_function.py
###

### settings.json
{
    "extensions" : ["*.hdr", "*.glb", "*.wasm"]
}

### lambda_function.py

anilktechie

{
  "StreamName": "$input.params('stream-name')"
}

anilktechie

#!/usr/bin/env python 
# based on  http://stackoverflow.com/questions/7052947/split-95mb-json-array-into-smaller-chunks
# usage: python json-split filename.json
# produces multiple filename_0.json of 1.49 MB size

import json
import sys

with open(sys.argv[1],'r') as infile:
    o = json.load(infile)

anilktechie

# Define S3 client
s3 = boto3.client(
    "s3",
    aws_access_key_id = access_key_id,
    aws_secret_access_key = secret_access_key
)

# Get object containing file to be staged
obj = s3.get_object(
    Bucket = "data-to-migrate",

anilktechie

import configparser

# Read AWS credentials from the config file
cfg_data = configparser.ConfigParser()
cfg_data.read('dl.cfg') 

# Save Redshift cluster
cluster_identifier = cfg_data["Redshift"]["cluster_identifier"]
cluster_type       = cfg_data["Redshift"]["cluster_type"]
node_type          = cfg_data["Redshift"]["node_type"]

anilktechie

###############################################
# Script settings and constants.
###############################################
SCRIPT_PATH = 'script.sql'

DB_CONNECTION = {
    'db_host': 'myhost.redshift.amazonaws.com', 
    'db_name': 'somedb', 
    'db_username': 'user',
    'db_password': 'pA$$word'