SQL Joins

-
Image
Joins are used to combine rows from two or more tables based on related columns between them. Joins allow you to retrieve data from multiple tables simultaneously, enabling you to create complex queries that fetch data from different sources. There are different types of joins in SQL, including: INNER JOIN Returns only the rows that have matching values in both tables based on the specified join condition. It discards non-matching rows from both tables. Example:           create table t1(x int); insert into t1 values(1); insert into t1 values(1); insert into t1 values(0); create table t2(y int); insert into t2 values(0); insert into t2 values(1); insert into t2 values(1);           select * from t1 inner join t2 on t1.x = t2.y Output: 2. LEFT JOIN (or) LEFT OUTER JOIN Returns all the rows from the left (or first) table and the matching rows from the rig...
Post a Comment

Postgresql Master/Slave Steaming Replication with Docker Container in Ubuntu 14

-

Install docker
Download Official Postgresql docker image from  https://hub.docker.com/_/postgres/
> docker pull postgres

Create folder docker_postgres and create a new file docker-compose.yml and paste the below information in it.
my-docker:
 image: postgres


Run the following command to create two docker containers with the name master and slave.
> docker run -d --name master postgres
> docker run -d --name slave postgres
To list the running docker containers
> sudo docker ps -a
To connect the running docker container.
> docker exec -ti master bash
Execute the following commands in both master and slave containers
Set the password for postgres user
> passwd postgres
Install openssh and vim
> apt-get update
> apt-get install openssh-server
> service ssh start
> apt-get install vim
To set default language locale
> dpkg-reconfigure locales
Locales to be generated: 146
Default locale for the system environment: 3
Create a new postgresql cluster and restart the service
> pg_createcluster 9.6 main --start
> service postgresql restart


Create folder and change the folder permission for ssh key generation
> cd /home/
> mkdir postgres
> chmod -R 777 postgres
Change as postgres user and generate ssh key
> su - postgres
> ssh-keygen
Repeat the above steps for slave container as well by connecting slave using the following command
> docker exec -ti slave bash
To get the IP Addresss for Master and Slave
> ip addr show
Master IP : 172.17.0.2
Slave IP  : 172.17.0.3


FOR VIRTUAL BOXs
Execute the following commands in both master and slave boxes
Install postgres.
Set the password for postgres user
> passwd postgres
Install openssh and vim
> apt-get update
> apt-get install openssh-server
> service ssh start
> apt-get install vim
Create folder and change the folder permission for ssh key generation
> cd /home/
> mkdir postgres
> chmod -R 777 postgres
Change as postgres user and generate ssh key
> su - postgres
> ssh-keygen
Repeat the above steps for slave container as well.
Master IP : 172.17.0.2
Slave IP  : 172.17.0.3


Master Configuration
Change as postgres user
> su - postgres
Copy the ssh-key to slave
> ssh-copy-id 172.17.0.3
Run the following statements to create the role rep and allow any number of connection to the role rep.
> psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'password';"
> psql -c "ALTER ROLE rep CONNECTION LIMIT -1;";
> exit
> cd /etc/postgresql/9.6/main
> vi pg_hba.conf
Add the following line in replication part
host replication rep 172.17.0.3/32 md5
save and close the file
> vi postgresql.conf
Uncomment and change the configs as follows:
wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 3
hot_standby = on
save and close the file
> service postgresql restart.
Slave Configuration
Stop the postgresql service
> service postgresql stop
Change as postgres user
> su - postgres
Copy the ssh-key to master
> ssh-copy-id 172.17.0.2
> exit
from root run the following to replicate the same initial db setup from master to slave
> rm -rf /var/lib/postgresql/9.6/main/*
> pg_basebackup --xlog-method=stream -D /var/lib/postgresql/9.6/main/ -U rep -h 172.17.0.2 -p 5433
> chmod -R g-rwx,o-rwx /var/lib/postgresql/9.6/main/ ; chown -R postgres.postgres /var/lib/postgresql/9.6/main/
> cd /etc/postgresql/9.6/main
> vi pg_hba.conf
Add the following line in replication part
host replication rep 172.17.0.2/32 md5
save and close the file
> vi postgresql.conf


Uncomment and change the configs as follows:
wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 3
hot_standby = on
save and close the file
> vi /var/lib/postgresql/9.6/main/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=172.17.0.2 port=5433 user=rep password=password'
trigger_file = '/tmp/failover.trigger'
save and close the file
> service postgresql restart.
To add another slave to the replication please follow all the steps in the Slave Configuration with respective changes.



Testing Master/Slave Replication
Master:
> su - postgres
> psql
> create table master(id integer);
> insert into t values(1);
Slave:
> su - postgres
> psql
> select * from t;
id
----
1
It will return the result set which is created in master


Comments

  1. SuryaJuly 2, 2020 at 3:52 AM

    The master container is unable to be accessed from outside. Any idea how to fix that?

    https://stackoverflow.com/questions/62664900/unable-to-connect-with-postgres-master-docker-container

    ReplyDelete

Post a Comment

Popular posts from this blog

Machine Learning Foundations - Deep Learning Summary - Quiz

-
1) Which of the following statements are true? (Check all that apply) a) Linear classifiers are never useful, because they cannot represent XOR. b) Linear classifiers are useful, because, with enough data, they can represent anything. c) Having good non-linear features can allow us to learn very accurate linear classifiers. d) none of the above 2) A simple linear classifier can represent which of the following functions? (Check all that apply) Hint: If you are stuck, see https://www.coursera.org/learn/ml-foundations/module/nqC1t/discussions/AAIUurrtEeWGphLhfbPAyQ a) x1 OR x2 OR NOT x3 b) x1 AND x2 AND NOT x3 c) x1 OR (x2 AND NOT x3) d) none of the above 3) Which of the the following neural networks can represent the following function? Select all that apply. (x1 AND x2) OR (NOT x1 AND NOT x2) Hint: If you are stuck, see https://www.coursera.org/learn/ml-foundations/module/nqC1t/discussions/AAIUurrtEeWGphLhfbPAyQ d 4) Which of the following statements is true? (Che...
Read more

Machine Learning Foundation - Deep Learning - Programming Assignment

-
1) What’s the least common category in the training data? a) bird b) dog c) cat d) automobile 2) Of the images below, which is the nearest ‘cat’ labeled image in the training data to the the first image in the test data (image_test[0:1])? f 3) Of the images below, which is the nearest ‘dog’ labeled image in the training data to the the first image in the test data (image_test[0:1])? d 4) For the first image in the test data, in what range is the mean distance between this image and its 5 nearest neighbors that were labeled ‘cat’ in the training data? a) 33 to 35 b) 35 to 37 c) 37 to 39 d) 39 to 41 e) Above 41 5) For the first image in the test data, in what range is the mean distance between this image and its 5 nearest neighbors that were labeled ‘dog’ in the training data? a) 33 to 35 b) 35 to 37 c) 37 to 39 d) 39 to 41 e) Above 41 6) On average, is the first image in the test data closer to its 5 nearest neighbors in the ‘cat’ data or in the ...
Read more

SQL Access WebApi with Basic Authentication

-
exec CALLWEBSERVICE 'username','password' CREATE PROCEDURE CALLWEBSERVICE(@Username NVARCHAR(50), @Password NVARCHAR(50)) AS     BEGIN     Declare @Object as Int;     DECLARE @authHeader VARCHAR(8000);        DECLARE @contentType VARCHAR(8000);     DECLARE @postData NVARCHAR(4000);     Declare @json as table(response nvarchar(max))     SET @postData = '{"method":"GetUserVehicles","userid":"1323","companyid":"0"}';        SET @contentType = 'application/json';     SET @authHeader = @Username + ':' + @Password;         SELECT         @authHeader = 'BASIC ' + CAST(N'' AS XML).value(               'xs:base64Binary(xs:hexBinary(sql:column("bin")))'             , 'VARCHAR(MAX)'...
Read more