Using proxy for apt-get

When your server is behind a proxy, you can do the following steps to run apt-get through a proxy:

open the file /etc/apt/apt.conf, if not create it and add the lines

Acquire::http::proxy "http://xxx\username:password@proxy.web.local:/3128";

save and exit. Now everything will go fine.

PostgreSQL PITR

PostgreSQL allows you to restore the database to a specific point in time by using

one of the three options:

recovery_target_name,recovery_target_time,recovery_target_xid,pause_at_recovery_target

Go to master node and do the following steps

Edit postgresql.conf and add\edit  the following lines as given below

listen_addresses = '*'

port = 5432

max_connections = 100

wal_level = hot_standby

archive_mode = on

archive_command = 'cp %p /usr/local/pgsql/archive/%f'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

For demo purpose we install two instance in the same host on two different ports.

Install the second instance on the port 5433

Open the pg_hba.conf in master and add these lines at the bottom

host    all     all     0.0.0.0/0       trust

host    replication     all     0.0.0.0/0       trust

host    replication     postgres        0.0.0.0/0       trust

Start the postgresql on port 5432 and create a test database and connect to it

create database test_pitr;

\c test_pitr

 create table test(col1 int, col2 char(10));

insert into test select generate_series(1,100),'test';

Go to data directory of secondary node and clear it,

 cd /usr/local/pgsql2/data

 rm -rf *

Connect  to secondary node at 5433 and execute pg_basebackup, which will take care of pg_start_backup('label'); and pg_stop_backup();

pg_basebackup -D /usr/local/pgsql2/data/ --write-recovery-conf --xlog-method=fetch --verbose -h localhost

This command will copy data folder from master with recovery.conf

You will get output something like  this

transaction log start point: 4/8D000028 on timeline 1

transaction log end point: 4/8E000050

pg_basebackup: base backup completee

Connect to master node on 5432 and execute the command to create restore point. The point till which the wal log will be replayed

 select pg_create_restore_point('patch_2015_09_17_2');

Now create some more records

insert into test select generate_series(101,1000),'test';

After this the data directory from master will be copied over to secondary. Open the postgresql.conf and edit as given below

hot_standby = on

And leave the rest as same

Open recovery.conf and edit it as below

standby_mode = 'on'

primary_conninfo = 'user=postgres host=localhost port=5432'

pause_at_recovery_target = true

recovery_target_name = patch_2015_09_17_02

Start the server on 5433

If we check the log file, these are the important sections:

entering standby mode..

recovery stopping at restore point "patch_of_2014_07_02", time

2014-07-02 12:08:57.507946+05:30

recovery has paused

Execute pg_xlog_replay_resume() to continue

Connect to psql on the secondary node and check the record

\c test_pitr

 select * from test order by col1 desc limit 2;

Try to create a table and you may find this error,

ERROR: cannot execute CREATE TABLE in a read-only transaction

The database is still in read-only mode.

Execute the following command

 SELECT pg_xlog_replay_resume();

It's done

Modules got blocked in powershell v1.0

The best way to unblock in v1.0 is to remove the module from powershell location
eg: C:\windows\system32\windowspowershell\v1.0\modules\pswindowsupdate

and extract the downloaded module again into the same location.

How to move putty session from one computer to another

http://mewbies.com/how_to_export_remove_putty_data_saved_sessions_tutorial.htm

Mysql replication failure

when faced with mysql replication failure problem,
"Please check the replication failure from Master Host"

do the following steps,
connect to the slave machine, open command prompt, it is cmd in windows or go to terminal in linux

connect to mysql as "mysql -u username -p", when prompt for password, enter the password
issue the command, "show slave status \G" and check for the errors.  Check the parameter which tells how long the slave is behind the master.

Do then,
1. stop slave;
2. start slave;
3. show slave status \G. Again check for erros and no of seconds the slave is behind the master

Even if this doesn't work try restarting the mysql server in slave machine, in worst case try doing it in master and then repeat the above steps

If any of the mysql table is crashed, then execute the following command as

repair table db.tablename;