### Replica Startup Output Example

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

This output indicates the successful restoration and startup of the replica server. It shows the data directory being moved, recovery.conf being written, and the postmaster starting.

```text
0 server starting
2012-01-27 16:58:45,709 31636 WARNING backup_datadir is disabled, deleting old data dir
2012-01-27 16:58:45,709 31636 INFO Move /tmp/test_slave_walmanager/backup//data.master to /tmp/test_slave/data
2012-01-27 16:58:45,766 31636 INFO Write /tmp/test_slave/data/recovery.conf
2012-01-27 16:58:45,768 31636 INFO Restoring configuration files
2012-01-27 16:58:45,771 31636 INFO Starting postmaster: /usr/lib/postgresql/9.1/bin//pg_ctl -D /tmp/test_slave/data -l /tmp/test_slave/data/postgresql.log start
```

--------------------------------

### Walmgr3 Slave Configuration Example

Source: https://github.com/markokr/skytools/blob/master/doc/walmgr3.txt

This is a sample configuration file for a walmgr3 slave. It defines paths for data, binaries, logs, and backup directories, as well as commands for stopping and starting the PostgreSQL service.

```ini
[walmgr]
job_name             = wal-slave
logfile              = ~/log/%(job_name)s.log
use_skylog           = 1

slave_data           = /var/lib/postgresql/9.1/main
slave_bin            = /usr/lib/postgresql/9.1/bin
slave_stop_cmd       = /etc/init.d/postgresql-9.1 stop
slave_start_cmd      = /etc/init.d/postgresql-9.1 start
slave_config_dir     = /etc/postgresql/9.1/main

# alternative pg_xlog directory for slave, symlinked to  pg_xlog on restore
#slave_pg_xlog        = /vol2/pg_xlog

walmgr_data          = ~/walshipping
completed_wals       = %(walmgr_data)s/logs.complete
partial_wals         = %(walmgr_data)s/logs.partial
full_backup          = %(walmgr_data)s/data.master
config_backup        = %(walmgr_data)s/config.backup

backup_datadir       = yes
keep_backups         = 0
archive_command =

# primary database connect string for hot standby -- enabling
# this will cause the slave to be started in hot standby mode.
#primary_conninfo = host=master port=5432 user=postgres

```

--------------------------------

### Setup Walmgr3 Master and Create Base Backup

Source: https://github.com/markokr/skytools/blob/master/doc/walmgr3.txt

Commands to set up the walmgr3 master configuration and initiate a base backup. The setup command prepares the master for archival, and the backup command creates the initial full backup.

```bash
master$ walmgr3 /var/lib/postgresql/conf/wal-master.ini setup
```

```bash
master$ walmgr3 /var/lib/postgresql/conf/wal-master.ini backup
```

--------------------------------

### Restore Backup and Start Replica with walmgr3

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Use this command to restore the backup to the replica server and start it. This process includes writing the recovery.conf file and starting the PostgreSQL postmaster.

```bash
walmgr3 /tmp/test_slave/wal-slave.ini restore
```

--------------------------------

### Successful Backup Output Example

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

This is an example of the expected output when a full backup is successfully completed by walmgr3. It logs the steps from obtaining the backup lock to successful completion.

```text
0  2012-01-27 16:58:31,464 30870 INFO Backup lock obtained.
2012-01-27 16:58:31,485 30750 INFO Execute SQL: select pg_start_backup('FullBackup'); [port=55401 host=/tmp dbname=template1]
2012-01-27 16:58:36,779 30750 INFO Checking tablespaces
2012-01-27 16:58:36,786 30750 INFO pg_log does not exist, skipping
2012-01-27 16:58:36,873 30750 INFO Backup conf files from /tmp/test_master
2012-01-27 16:58:38,599 31256 INFO First useful WAL file is: 000000010000000000000002
2012-01-27 16:58:45,442 31633 INFO Backup lock released.
2012-01-27 16:58:45,461 30750 INFO Full backup successful
```

--------------------------------

### Start PostgreSQL Master Server

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Command to start the PostgreSQL master server using pg_ctl.

```bash
/usr/lib/postgresql/9.1/bin/pg_ctl -D /tmp/test_master -l /tmp/test_master/postgresql.log start
```

--------------------------------

### Start pgqd ticker daemon

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

Command to start the pgqd ticker daemon, which is necessary for londiste3 replication workers to function. Requires a configuration file.

```bash
$pgqd -v -d conf/pgqd.ini
```

--------------------------------

### Create and Start Londiste Shard Node 0

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Creates the first leaf node on sharddb_0 and starts its Londiste worker. The '--provider' argument specifies the root node.

```bash
londiste3 st3partsplit/st3_sharddb_0.ini create-leaf node2_0 dbname=sharddb_0 --provider=dbname=rootdb
londiste3 -d st3partsplit/st3_sharddb_0.ini worker
```

--------------------------------

### Example queue_splitter3 configuration

Source: https://github.com/markokr/skytools/blob/master/doc/queue_splitter3.txt

This configuration defines the job name, source and destination databases, the PgQ queue name, and log/PID file locations. Adjust 'dbname' and 'sourceq' to match your environment.

```ini
[queue_splitter3]
job_name        = queue_spliter_sourcedb_sourceq_targetdb

src_db          = dbname=sourcedb
dst_db          = dbname=targetdb

pgq_queue_name  = sourceq

logfile         = ~/log/%(job_name)s.log
pidfile         = ~/pid/%(job_name)s.pid
```

--------------------------------

### Start Londiste Ticker

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_cascaded_rep_howto.txt

Run the ticker process in the background to manage replication queues. Use the ticker configuration file.

```bash
pgqd -q -d conf/pgqd.ini
```

--------------------------------

### Start PGQD Ticker

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Starts the 'pgqd' process in daemon mode. This process is responsible for triggering Londiste jobs.

```bash
pgqd -d st3partsplit/pgqd.ini
```

--------------------------------

### Create and Start Londiste Shard Node 1

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Creates the second leaf node on sharddb_1 and starts its Londiste worker. Similar to node 0, it points to the root node.

```bash
londiste3 st3partsplit/st3_sharddb_1.ini create-leaf node2_1 dbname=sharddb_1 --provider=dbname=rootdb
londiste3 -d st3partsplit/st3_sharddb_1.ini worker
```

--------------------------------

### Launch queue_splitter3 in daemon mode

Source: https://github.com/markokr/skytools/blob/master/doc/queue_splitter3.txt

Use this command to start the queue splitter as a background process. Ensure your configuration file is correctly specified.

```bash
queue_splitter3 queue_splitter_sourcedb_sourceq_targetdb.ini -d
```

--------------------------------

### Launch PgQ Ticker Daemon

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

Starts the PgQ ticker daemon in the background, essential for managing replication events.

```bash
pgqd -d st3simple/pgqd.ini
```

--------------------------------

### Example queue_mover3 Configuration

Source: https://github.com/markokr/skytools/blob/master/doc/queue_mover3.txt

This INI file configures the queue_mover3 job, specifying source and target databases, queue names, and logging paths.

```ini
[queue_mover3]
job_name = eventlog_to_target_mover
src_db = dbname=sourcedb
dst_db = dbname=targetdb
pgq_queue_name = eventlog
dst_queue_name = copy_of_eventlog
pidfile = log/%(job_name)s.pid
logfile = pid/%(job_name)s.log
```

--------------------------------

### Restore Walmgr3 Configuration on Slave

Source: https://github.com/markokr/skytools/blob/master/doc/walmgr3.txt

Command to prepare the PostgreSQL configuration on the slave and start WAL replay. This command moves data in place, creates a recovery.conf file, and starts the PostgreSQL server in recovery mode.

```bash
slave$ walmgr3 /var/lib/postgresql/conf/wal-slave.ini restore
```

--------------------------------

### Configure Londiste Ticker (pgqd)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_cascaded_rep_howto.txt

Configure the ticker to manage replication across multiple databases. Ensure the database list matches your setup.

```ini
[pgqd]
database_list = db1,db2,db3,db4,db5
logfile = log/pgqd.log
pidfile = pid/pgqd.pid
```

--------------------------------

### Start Londiste Worker Processes

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_cascaded_rep_howto.txt

Launch worker processes on each node to execute replication tasks. Ensure a worker is running for each configured node.

```bash
londiste3 -q -d conf/londiste_db1.ini worker
londiste3 -q -d conf/londiste_db2.ini worker
londiste3 -q -d conf/londiste_db3.ini worker
londiste3 -q -d conf/londiste_db4.ini worker
londiste3 -q -d conf/londiste_db5.ini worker
```

--------------------------------

### Launch queue_mover3 in Daemon Mode

Source: https://github.com/markokr/skytools/blob/master/doc/queue_mover3.txt

Start the queue_mover3 process as a daemon using the '-d' flag and specifying the configuration file.

```bash
$ queue_mover3 -d qmover_sourceq_to_targetdb.ini
```

--------------------------------

### Check Initial Replicated State on Slave

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Connect to the slave server using psql to verify the initial state of the replicated data. This example checks the 'pgbench_tellers' table.

```sql
psql -h /tmp -p 55402 pgbdb
psql (9.1.1)
Type "help" for help.

pgbdb=# select * from pgbench_tellers;
 tid | bid | tbalance | filler 
-----+-----+----------+--------
   1 |   1 |        0 | 
   2 |   1 |        0 | 
   3 |   1 |        0 | 
   4 |   1 |        0 | 
   5 |   1 |        0 | 
   6 |   1 |        0 | 
   7 |   1 |        0 | 
   8 |   1 |        0 | 
   9 |   1 |        0 | 
  10 |   1 |        0 | 
(10 rows)
```

--------------------------------

### Create a table for event data

Source: https://github.com/markokr/skytools/blob/master/doc/queue_splitter3.txt

Define the schema for your event data. This example creates a table 'queue.event1' with 'id' and 'name' fields. Ensure the primary key is set correctly.

```sql
CREATE SCHEMA queue;
CREATE TABLE queue.event1 (
     -- this should correspond to event internal structure
	-- here you can put checks that correct data is put into queue
   	id int4,
	name text,
	-- not needed, but good to have:
	primary key (id)
   );
```

--------------------------------

### Create and Checkout New Git Branch

Source: https://github.com/markokr/skytools/blob/master/doc/devnotes.txt

Create a new branch for your changes, starting from the currently checked out branch. You can then checkout this new branch to begin working on it. This can be done in two steps or one.

```bash
git branch mybranch
git checkout mybranch
```

```bash
git checkout -b mybranch
```

--------------------------------

### Activate Git Tab Completion (Bash)

Source: https://github.com/markokr/skytools/blob/master/doc/devnotes.txt

Source the Git completion script into your .bashrc to enable tab completion for Git commands. Choose the appropriate method based on your Git installation.

```bash
# 1) use unpacked source tree
source $git_src_tree/contrib/completion/git-completion.bash

# 2) use packaged git (preferred)
source /etc/bash_completion.d/git

# 3) use packaged git, turn extended completion for everything
#    [ markokr: buggy completion modules can be rather annoying
```

--------------------------------

### Get PgQ Batch Information

Source: https://github.com/markokr/skytools/blob/master/doc/pgq-sql.txt

Retrieves detailed information about a specific batch, including the associated queue and consumer, start and end times, and ticker lag.

```sql
pgq.get_batch_info(batch_id)
```

--------------------------------

### Populate Database with pgbench

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

This command initializes the database with the pgbench schema and sample data. Adjust scale factor (-s) and number of fields (-F) as needed.

```bash
pgbench -i -s 2 -F 80 l3simple
```

--------------------------------

### Create and Initialize pgbench Database

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Commands to create a new database and initialize it with pgbench data for testing.

```bash
createdb pgbdb;
```

```bash
/usr/lib/postgresql/9.1/bin/pgbench -i -s 1 -F 80 pgbdb -h /tmp -p 55401
```

--------------------------------

### Create root node on partition database (part1)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

Command to create a londiste3 root node on the 'part1' database. Requires a configuration file and specifies the database name.

```bash
$ londiste3 -v conf/l3_part1_q_part1.ini create-root part1_root dbname=part1
```

--------------------------------

### Create root node on partition database (part2)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

Command to create a londiste3 root node on the 'part2' database. Requires a configuration file and specifies the database name.

```bash
$ londiste3 -v conf/l3_part2_q_part2.ini create-root part2_root dbname=part2
```

--------------------------------

### Initialize Londiste Nodes

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_cascaded_rep_howto.txt

Create root and branch nodes for the replication topology. Use the appropriate provider for branch nodes.

```bash
londiste3 -q conf/londiste_db1.ini create-root node1 dbname=db1
londiste3 -q conf/londiste_db2.ini create-branch node2 dbname=db2 --provider=dbname=db1
londiste3 -q conf/londiste_db3.ini create-branch node3 dbname=db3 --provider=dbname=db1
londiste3 -q conf/londiste_db4.ini create-branch node4 dbname=db4 --provider=dbname=db2
londiste3 -q conf/londiste_db5.ini create-branch node5 dbname=db5 --provider=dbname=db3
```

--------------------------------

### Start Londiste Root Worker

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Starts the Londiste worker process on the root node. The '-d' flag runs it in daemon mode.

```bash
londiste3 -d st3partsplit/st3_rootdb.ini worker
```

--------------------------------

### Create table on partition database

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

SQL command to create a sample table 'mydata' with 'id' and 'data' columns on a partition database. This table will be replicated.

```sql
create table mydata (id int4 primary key, data text)
```

--------------------------------

### Test Replication with pgbench

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

Runs pgbench at full speed for a short duration to generate traffic and test the replication's responsiveness.

```bash
pgbench -T 10 -c 5 l3simple
```

--------------------------------

### Get Batch Info

Source: https://github.com/markokr/skytools/blob/master/doc/pgq-sql.txt

Retrieves detailed information about a specific batch.

```APIDOC
## get_batch_info

### Description
Retrieves detailed information about a specific event batch.

### Signature
`pgq.get_batch_info(batch_id int8)`

### Parameters
- `batch_id` (int8): The identifier of the batch.

### Returns
A single row containing the following fields:
- `queue_name` (text): Name of the queue.
- `consumer_name` (text): Name of the consumer processing the batch.
- `batch_start` (timestamptz): Timestamp when the batch processing started.
- `batch_end` (timestamptz): Timestamp when the batch processing ended.
- `prev_tick_id` (int8): Identifier of the previous tick.
- `tick_id` (int8): Identifier of the current tick.
- `lag` (integer): Lag associated with the batch.
```

--------------------------------

### Initialize pgbench Schema

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Initializes the pgbench schema and populates it with sample data in the root database. Adjust scale factor (-s) and fill factor (-F) as needed.

```bash
/usr/lib/postgresql/8.4/bin/pgbench -i -s 2 -F 80 rootdb
```

--------------------------------

### Create Databases

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Creates the necessary databases for the root and shard nodes. Ensure you have the 'psql' command-line tool available.

```bash
psql -c "CREATE DATABASE rootdb;"
psql -c "CREATE DATABASE sharddb_0;"
psql -c "CREATE DATABASE sharddb_1;"
```

--------------------------------

### Get Queue Info

Source: https://github.com/markokr/skytools/blob/master/doc/pgq-sql.txt

Retrieves information about all active event queues.

```APIDOC
## get_queue_info

### Description
Retrieves a list of all active event queues and their associated metadata.

### Signature
`pgq.get_queue_info()`

### Returns
A set of rows, where each row contains the following fields:
- `queue_name` (text): Name of the queue.
- `queue_ntables` (integer): Number of tables used by the queue.
- `queue_cur_table` (integer): Current table index.
- `queue_rotation_period` (interval): Period for table rotation.
- `queue_switch_time` (timestamptz): Time of the last table switch.
- `queue_external_ticker` (boolean): Whether an external ticker is used.
- `queue_ticker_max_count` (integer): Maximum event count for ticker.
- `queue_ticker_max_lag` (interval): Maximum allowed ticker lag.
- `queue_ticker_idle_period` (interval): Idle period for the ticker.
- `ticker_lag` (interval): Current ticker lag.
```

--------------------------------

### Launch londiste3 replication worker (part2)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

Command to launch a londiste3 replication worker daemon for the 'part2' to 'full1' replication. Requires a configuration file.

```bash
$londiste3 -v -d conf/l3_part2_q_full1.ini worker
```

--------------------------------

### Get Consumer Info

Source: https://github.com/markokr/skytools/blob/master/doc/pgq-sql.txt

Retrieves information about active consumers, optionally filtered by queue or specific consumer.

```APIDOC
## get_consumer_info

### Description
Retrieves information about active consumers. This can be filtered by queue name or a specific consumer ID.

### Signatures
- `pgq.get_consumer_info()`
- `pgq.get_consumer_info(queue_name text)`
- `pgq.get_consumer_info(queue_name text, consumer text)`

### Parameters
- `queue_name` (text, optional): The name of the queue to filter by.
- `consumer` (text, optional): The identifier of the consumer to filter by.

### Returns
A set of rows, where each row contains the following fields:
- `queue_name` (text): Name of the queue.
- `consumer_name` (text): Name of the consumer.
- `lag` (integer): Current lag of the consumer.
- `last_seen` (timestamptz): Timestamp of the last seen activity.
- `last_tick` (timestamptz): Timestamp of the last tick.
- `current_batch` (int8): Identifier of the current batch being processed.
- `next_tick` (timestamptz): Timestamp of the next scheduled tick.
```

--------------------------------

### Launch londiste3 replication worker (part1)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

Command to launch a londiste3 replication worker daemon for the 'part1' to 'full1' replication. Requires a configuration file.

```bash
$londiste3 -v -d conf/l3_part1_q_full1.ini worker
```

--------------------------------

### Initialize Master and Slave with SSH Keys using walmgr3

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Use this command to set up passwordless SSH access between master and slave servers and initialize walmgr3 configuration.

```bash
master$ walmgr3 --ssh-keygen --init-master --slave <slave_hostname>
```

```bash
slave$ walmgr3 --init-slave --ssh-add-key=/tmp/id_rsa.pub
```

--------------------------------

### Create Source and Target Queues with pgqadm.py

Source: https://github.com/markokr/skytools/blob/master/doc/queue_mover3.txt

Use pgqadm.py to create the source and target queues. Ensure you provide the correct ticker configuration file and queue names.

```bash
$ pgqadm.py sourcedb_ticker.ini create <srcqueue>
$ pgqadm.py targetdb_ticker.ini create <dstqueue>
```

--------------------------------

### Get Batch Events

Source: https://github.com/markokr/skytools/blob/master/doc/pgq-sql.txt

Retrieves all events within a specified batch. The batch must still be closed using `pgq.finish_batch()`.

```APIDOC
## get_batch_events

### Description
Returns a set of events belonging to a specific batch. Note that a batch may be empty.

### Signature
`pgq.get_batch_events(batch_id int8)`

### Parameters
- `batch_id` (int8): The identifier of the batch.

### Returns
A set of events, where each event has the following fields:
- `ev_id` (int8): Event unique identifier.
- `ev_time` (timestamptz): Timestamp when the event was created.
- `ev_txid` (int8): Transaction ID that created the event.
- `ev_retry` (int4): Number of retries for this event.
- `ev_type` (text): Type of the event.
- `ev_data` (text): Data payload of the event.
- `ev_extra1` to `ev_extra4` (any): Additional data fields.
```

--------------------------------

### Populate Partition Configuration

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Applies the partconf.sql script to all databases and inserts initial configuration values into the 'partconf.conf' table on shard databases.

```bash
psql rootdb < partconf.sql
psql sharddb_0 < partconf.sql
psql sharddb_1 < partconf.sql
psql sharddb_0 -c "insert into partconf.conf(part_nr, max_part) values(0,1);"
psql sharddb_1 -c "insert into partconf.conf(part_nr, max_part) values(1,1);"
```

--------------------------------

### Update Table on Master

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Perform an update on the master server to test replication. This example updates the 'filler' column in the 'pgbench_tellers' table.

```sql
pgbdb=# \q
ubuntu@ubuntu-VirtualBox:~/skytools-markokr/python/testwrappers$ psql -h /tmp -p 55401 pgbdb
psql (9.1.1)
Type "help" for help.

pgbdb=# update pgbench_tellers set filler = random();
UPDATE 10
pgbdb=# select * from pgbench_tellers;
 tid | bid | tbalance |                                        filler                                        
-----+-----+----------+--------------------------------------------------------------------------------------
```

--------------------------------

### Create and Populate Table on Root Node

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_cascaded_rep_howto.txt

Create a new table and insert data into it on the root database. This data will then be replicated.

```bash
psql -d db1 -c "create table mytable (id serial primary key, data text)"
psql -d db1 -c "insert into mytable (data) values ('row1')"
psql -d db1 -c "insert into mytable (data) values ('row2')"
psql -d db1 -c "insert into mytable (data) values ('row3')"
psql -d db1 -c "insert into mytable (data) values ('row4')"
```

--------------------------------

### Generate Background Data Traffic

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

Runs pgbench in the background with a throttled workload to simulate ongoing database activity during replication setup.

```bash
pgbench -T 120 -c 5 l3simple -f /tmp/throttled.pgbench
```

--------------------------------

### Run Londiste Leaf Worker Daemon

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

Starts the Londiste worker daemon for the leaf node, enabling it to receive and apply replicated data.

```bash
londiste3 -d st3simple/st3_l3simple_leaf.ini worker
```

--------------------------------

### Configure londiste3 root node for partition database (part2)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

INI file for setting up a londiste3 root node on a partition database. Specifies job name, database connection, queue name, log file, and PID file.

```ini
[londiste3]
job_name = l3_part2_q_part2
db = dbname=part2
queue_name = l3_part2_q
logfile = log/%(job_name)s.log
pidfile = pid/%(job_name)s.pid
```

--------------------------------

### Run Londiste Root Worker Daemon

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

Starts the Londiste worker daemon in debug mode for the root node, processing replication tasks.

```bash
londiste3 -d st3simple/st3_l3simple_primary.ini worker
```

--------------------------------

### Configure londiste3 root node for partition database (part1)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

INI file for setting up a londiste3 root node on a partition database. Specifies job name, database connection, queue name, log file, and PID file.

```ini
[londiste3]
job_name = l3_part1_q_part1
db = dbname=part1
queue_name = l3_part1_q
logfile = log/%(job_name)s.log
pidfile = pid/%(job_name)s.pid
```

--------------------------------

### Perform a Full Backup with walmgr3

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Execute this command to create a full backup of the master database using walmgr3. The backup includes data, WAL files, and configuration files.

```bash
walmgr3 /tmp/test_master/wal-master.ini backup
```

--------------------------------

### Create leaf node on full database (part1)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

Command to create a londiste3 leaf node on the 'full1' database, specifying the provider database ('part1'). This node will receive data from 'part1'.

```bash
$ londiste3 -v conf/l3_part1_q_full1.ini create-leaf merge_part1_full1 dbname=full1 --provider=dbname=part1
```

--------------------------------

### Configure PostgreSQL for Replication with walmgr3

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Run this command to set up the master PostgreSQL server for replication using walmgr3. This modifies the postgresql.conf file.

```bash
walmgr3 /tmp/test_master/wal-master.ini setup
```

--------------------------------

### Create Londiste Root Node

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

Command to initialize the Londiste root node. Requires the configuration file, node name, and the master database connection string.

```bash
londiste3 st3simple/st3_l3simple_primary.ini create-root node1 "dbname=l3simple host=10.199.59.194"
```

--------------------------------

### Require Skytools 3.0 Module

Source: https://github.com/markokr/skytools/blob/master/doc/skytools3.txt

Use pkgloader to ensure Skytools 3.0 is available before importing. This allows parallel installation with Skytools 2.

```python
import pkgloader
pkgloader.require('skytools', '3.0')
import skytools
```

--------------------------------

### Get PgQ Queue Information

Source: https://github.com/markokr/skytools/blob/master/doc/pgq-sql.txt

Retrieves a list of all active queues and their associated metadata, including table information, rotation periods, and ticker settings.

```sql
pgq.get_queue_info()
```

--------------------------------

### Create leaf node on full database (part2)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

Command to create a londiste3 leaf node on the 'full1' database, specifying the provider database ('part2'). This node will receive data from 'part2'.

```bash
$ londiste3 -v conf/l3_part2_q_full1.ini create-leaf merge_part2_full1 dbname=full1 --provider=dbname=part2
```

--------------------------------

### Get Current Transaction Epoch in PostgreSQL

Source: https://github.com/markokr/skytools/blob/master/doc/faq.txt

Use this SQL query to retrieve the current transaction ID epoch from a running PostgreSQL database. This is useful for understanding the state of transaction IDs.

```sql
SELECT (txid_current() >> 32) as epoch;
```

--------------------------------

### Initialize Master with Slave Hostname

Source: https://github.com/markokr/skytools/blob/master/doc/walmgr3.txt

Command to initialize the walmgr3 master, setting up SSH keys and specifying the slave hostname. This is a prerequisite for establishing communication between master and slave.

```bash
walmgr3 --ssh-keygen --init-master --slave <slave_hostname>
```

--------------------------------

### Add Table to Root and Partitions with Partition Handler

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Adds the 'pgbench_accounts' table for replication. The '--handler=part' and '--handler-arg=key=aid' specify partitioning by the 'aid' column using the 'part' handler. '--create' on shard nodes ensures the table schema is created there.

```bash
londiste3 st3partsplit/st3_rootdb.ini add-table pgbench_accounts --handler=part --handler-arg=key=aid
londiste3 st3partsplit/st3_sharddb_0.ini add-table pgbench_accounts --create --handler=part --handler-arg=key=aid
londiste3 st3partsplit/st3_sharddb_1.ini add-table pgbench_accounts --create --handler=part --handler-arg=key=aid
```

--------------------------------

### Check PostgreSQL Server Status and Connections

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Use tail to check the server log for startup messages and psql to verify connectivity.

```bash
tail /tmp/test_master/postgresql.log
```

```bash
psql -h /tmp -p 55401 -l
```

--------------------------------

### Get PgQ Consumer Information

Source: https://github.com/markokr/skytools/blob/master/doc/pgq-sql.txt

Retrieves information about active consumers, optionally filtered by queue name or a specific consumer. The results include lag, last seen time, and current batch details.

```sql
pgq.get_consumer_info()
```

```sql
pgq.get_consumer_info(queue_name)
```

```sql
pgq.get_consumer_info(queue_name, consumer)
```

--------------------------------

### Add Tellers Table to Root Node

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Adds the 'pgbench_tellers' table to the root node for replication, partitioning by the 'tid' column. This example only shows adding to the root, implying shard configuration would follow a similar pattern.

```bash
londiste3 st3partsplit/st3_rootdb.ini add-table pgbench_tellers --handler=part --handler-arg=key=tid
```

--------------------------------

### Set up Passwordless SSH using ssh-keygen and ssh

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Alternative method to set up passwordless SSH access from master to slave using standard ssh tools.

```bash
master$ test -f ~/.ssh/id_dsa.pub || ssh-keygen -t dsa
master$ cat ~/.ssh/id_dsa.pub | ssh slave cat >> .ssh/authorized_keys
```

--------------------------------

### Configure londiste3 leaf node for full database (part2)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

INI file for setting up a londiste3 leaf node on the 'full1' database, receiving data from 'part2'. Specifies job name, database connection, queue name, log file, and PID file.

```ini
[londiste3]
job_name = l3_part2_q_full1
db = dbname=full1
queue_name = l3_part2_q
logfile = log/%(job_name)s.log
pidfile = pid/%(job_name)s.pid
```

--------------------------------

### Configure londiste3 leaf node for full database (part1)

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

INI file for setting up a londiste3 leaf node on the 'full1' database, receiving data from 'part1'. Specifies job name, database connection, queue name, log file, and PID file.

```ini
[londiste3]
job_name = l3_part1_q_full1
db = dbname=full1
queue_name = l3_part1_q
logfile = log/%(job_name)s.log
pidfile = pid/%(job_name)s.pid
```

--------------------------------

### Run pgbench Test

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Execute the pgbench utility to stress test the database. This command runs pgbench with 5 parallel connections for 10 seconds.

```bash
/usr/lib/postgresql/8.4/bin/pgbench -T 10 -c 5 rootdb
```

--------------------------------

### Subscribe table on full database with creation and merge

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

Command to subscribe the 'mydata' table on the 'full1' database. `--create` ensures the table is created on the leaf node if it doesn't exist, and `--merge-all` subscribes it from all relevant queues.

```bash
$londiste3 -v conf/l3_part1_q_full1.ini add-table mydata --create --merge-all
```

--------------------------------

### Create and Dump Target Database Schema

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

Creates a new database for the slave and then dumps the schema from the master database into it. Ensure 'l3simple_db1' is the master and 'l3simple_db2' is the slave.

```bash
psql -d postgres -c "CREATE DATABASE l3simple_db2;"
pg_dump -s l3simple_db1 | psql l3simple_db2
```

--------------------------------

### Create Londiste Root Node

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Initializes the Londiste root node. This command sets up the necessary metadata in the root database.

```bash
londiste3 st3partsplit/st3_rootdb.ini create-root node1 dbname=rootdb
```

--------------------------------

### Add Partitioned Table with Londiste3

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_partitioning_howto.txt

Use this command to add a table to Londiste3 for partitioning. Specify the configuration file, table name, and handler arguments for partitioning.

```bash
londiste3 st3partsplit/st3_sharddb_0.ini add-table pgbench_tellers --create --handler=part --handler-arg=key=tid
```

```bash
londiste3 st3partsplit/st3_sharddb_1.ini add-table pgbench_tellers --create --handler=part --handler-arg=key=tid
```

--------------------------------

### Initialize PostgreSQL Master Data Directory

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Command to create the data directory structure for a PostgreSQL master server.

```bash
/usr/lib/postgresql/9.1/bin/initdb -D /tmp/test_master
```

--------------------------------

### Connect to PostgreSQL Replica

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Connect to the PostgreSQL replica database using its specific host and port. This verifies the replica's accessibility.

```bash
psql -h /tmp -p 55402 pgbdb
```

--------------------------------

### Configure PostgreSQL Master Server Settings

Source: https://github.com/markokr/skytools/blob/master/doc/howto/setup_walmgr_replication.txt

Edit the postgresql.conf file to set the socket directory, port, and enable logging for the master server.

```ini
unix_socket_directory = /tmp
port = 55401
# optional, for logging
log_connections = on
log_disconnections = on
```

--------------------------------

### Create PgQ Event Queue

Source: https://github.com/markokr/skytools/blob/master/doc/pgq-sql.txt

Initializes an event queue. Returns 0 if the queue already exists, 1 otherwise.

```sql
select pgq.create_queue('LogEvent');
```

--------------------------------

### Initialize Slave and Add SSH Key

Source: https://github.com/markokr/skytools/blob/master/doc/walmgr3.txt

Command to initialize the walmgr3 slave and add the public SSH key from the master. This enables passwordless SSH authentication from the master to the slave.

```bash
walmgr3 --init-slave --ssh-add-key=/tmp/id_rsa.pub
```

--------------------------------

### Insert data into partition table

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_merge_howto.txt

SQL commands to insert sample data into the 'mydata' table on partition databases. This data will be replicated to the full database.

```sql
insert into mydata values (1, 'part1')
```

```sql
insert into mydata values (2, 'part2')
```

--------------------------------

### Initialize Londiste Leaf Node

Source: https://github.com/markokr/skytools/blob/master/doc/howto/londiste3_simple_rep_howto.txt

Initializes the Londiste leaf node in the target database, linking it to the master provider. Requires the leaf config file, node name, target DB name, and provider connection string.

```bash
londiste3 st3simple/st3_l3simple_leaf.ini create-leaf node2 dbname=l3simple --provider="dbname=l3simple host=10.199.59.194"
```