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

Automate failover in postgres replication using repmgr

-
repmgr.conf
===========
failover=automatic
pg_bindir='/usr/lib/postgresql/9.5/bin'
promote_command='repmgr standby promote -f /etc/repmgr/repmgr.conf'
follow_command='repmgr standby follow -f /etc/repmgr/repmgr.conf'

note pg_bindir property otherwise you will pg_ctl unknown command error

postgrsql.conf
==============
shared_preload_libraries = 'repmgr_funcs'

repmgrd helps to monitor the replication , with -d/--daemonize option you can run this as a deamon process
Run the following command in standby server 1 & 2 alone , create a log file as postgres user otherwise you will get permission issue

> sudo su - postgres
> vi repmgr.log
> repmgrd -f /etc/repmgr/repmgr.conf --verbose > repmgr.log 2>&1

repmgr=# select * from repmgr_cluster.repl_nodes order by id;
 id |  type   | upstream_node_id | cluster | name  |                   conninfo                   |   slot_name   | priority | active
----+---------+------------------+---------+-------+----------------------------------------------+---------------+----------+--------
  1 | master  |                  | cluster | node1 | host=127.0.0.1 user=repmgr dbname=repmgr | repmgr_slot_1 |      100 | t
  2 | standby |                1 | cluster | node2 | host=127.0.0.2 user=repmgr dbname=repmgr | repmgr_slot_2 |      100 | t
  3 | standby |                1 | cluster | node3 | host=127.0.0.3 user=repmgr dbname=repmgr | repmgr_slot_3 |      100 | t
(3 rows)

all repmgrd events will be logged in  as

repmgr=# SELECT * FROM repmgr_cluster.repl_events;
 node_id |      event       | successful |        event_timestamp        |                                        details                                      
---------+------------------+------------+-------------------------------+---------------------------------------------------------------------------------------
       1 | master_register  | t          | 2017-11-06 11:08:54.031239+00 |
       2 | standby_clone    | t          | 2017-11-06 11:42:54.17556+00  | Cloned from host '127.0.0.1', port 5432; backup method: pg_basebackup; --force: N
       2 | standby_register | t          | 2017-11-06 11:44:55.663162+00 |
       3 | standby_clone    | t          | 2017-11-06 12:26:03.452454+00 | Cloned from host '127.0.0.1', port 5432; backup method: pg_basebackup; --force: N
       3 | standby_register | t          | 2017-11-06 12:26:42.327856+00 |
       2 | repmgrd_start    | t          | 2017-11-06 12:47:08.406556+00 |
       3 | repmgrd_start    | t          | 2017-11-06 12:50:05.176384+00 |
(7 rows)

Now stop the master and check

> sudo su - postgres
> /usr/lib/postgresql/9.5/bin/pg_ctl -D /var/lib/postgresql/9.5/main -m immediate stop

Standby1
[2017-11-07 05:35:14] [NOTICE] this node is the best candidate to be the new master, promoting...
[2017-11-07 05:35:14] [ERROR] connection to database failed: could not connect to server: Connection refused
        Is the server running on host "127.0.0.1" and accepting
        TCP/IP connections on port 5432?

[2017-11-07 05:35:14] [NOTICE] promoting standby
[2017-11-07 05:35:14] [NOTICE] promoting server using '/usr/lib/postgresql/9.5/bin/pg_ctl -D /var/lib/postgresql/9.5/main promote'
[2017-11-07 05:35:16] [NOTICE] STANDBY PROMOTE successful

Standby2
[2017-11-07 05:35:19] [ERROR] connection to database failed: could not connect to server: Connection refused
        Is the server running on host "127.0.0.1" and accepting
        TCP/IP connections on port 5432?

[2017-11-07 05:35:19] [NOTICE] restarting server using '/usr/lib/postgresql/9.5/bin/pg_ctl  -w -D /var/lib/postgresql/9.5/main -m fast restart'


 node_id |          event           | successful |        event_timestamp        |                                        details                                      
---------+--------------------------+------------+-------------------------------+---------------------------------------------------------------------------------------
       1 | master_register          | t          | 2017-11-07 05:18:24.077547+00 |
       2 | standby_clone            | t          | 2017-11-07 05:24:22.048421+00 | Cloned from host '127.0.0.1', port 5432; backup method: pg_basebackup; --force: N
       3 | standby_clone            | t          | 2017-11-07 05:25:16.287048+00 | Cloned from host '127.0.0.1', port 5432; backup method: pg_basebackup; --force: N
       2 | standby_register         | t          | 2017-11-07 05:25:57.589019+00 |
       3 | standby_register         | t          | 2017-11-07 05:27:47.635254+00 |
       2 | repmgrd_start            | t          | 2017-11-07 05:33:34.189278+00 |
       3 | repmgrd_start            | t          | 2017-11-07 05:33:37.771893+00 |
       2 | standby_promote          | t          | 2017-11-07 05:35:16.960249+00 | Node 2 was successfully promoted to master
       2 | repmgrd_failover_promote | t          | 2017-11-07 05:35:16.966643+00 | node 2 promoted to master; old master 1 marked as failed
       3 | repmgrd_failover_follow  | t          | 2017-11-07 05:35:26.987551+00 | Node 3 now following new upstream node 2
(10 rows)



repmgr=# select * from repmgr_cluster.repl_nodes order by id;
 id |  type   | upstream_node_id | cluster | name  |                   conninfo                   |   slot_name   | priority | active
----+---------+------------------+---------+-------+----------------------------------------------+---------------+----------+--------
  1 | master  |                  | cluster | node1 | host=127.0.0.1 user=repmgr dbname=repmgr | repmgr_slot_1 |      100 | f
  2 | master  |                  | cluster | node2 | host=127.0.0.2 user=repmgr dbname=repmgr | repmgr_slot_2 |      100 | t
  3 | standby |                2 | cluster | node3 | host=127.0.0.3 user=repmgr dbname=repmgr | repmgr_slot_3 |      100 | t
(3 rows)

Testing:
==================

In Master,

postgres=# create table test(id integer);
CREATE TABLE
postgres=# insert into test values(1);
INSERT 0 1
postgres=# select * from test ;
 id
----
  1
(1 row)

Same data will be replicated in standby server 1 and 2

postgres=# select * from test ;
 id
----
  1
(1 row)

Comments

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