Could not open file "pg_clog/0XXX"

I need to vacuum full the database but got this error

mydb=# vacuum full;
ERROR:  could not access status of transaction 376865319
DETAIL:  Could not open file "pg_clog/0167": No such file or directory.




Solution per searching google.

-bash-3.2$ dd if=/dev/zero of=/pgsql/data/pg_clog/0167 bs=256k count=1


Then VACCUM FULL again.

Fixing iscsi connectivity issue between Centos 6 and Compellent Storage

Fixing iscsi connectivity issue between Centos 6 and Compellent Storage


I boot a Centos 6 server so it can be mounted with a volume from compellent, but got this issue that the HBA interface cannot be detected at the compellent side but check the connectivity, it able to established connection to iscsi target.


I manually connect the host to the compellent server iscsi ip, so below, I have 8 IP to connect

#connecting
for i in 1 2 3 4 5 6 7 8; do iscsiadm --mode discoverydb --type sendtargets --portal 1.2.3.$i --discover; done

#logging in
iscsiadm -m node -l



#connecting
But i encountered at the compellent an issue where the existing initiator name does not show, so I cannot bind the volume to that said host at compellent.

The fix is to rename the initiator name. Use the command

/sbin/iscsi-iname


The above will generate a new name and then put it at /etc/iscsi/initiatorname.iscsi



If you have an existing InitiatorName=iqn.1994-05.com.redhat:fc5b3050bf

and that name either defined on different IP, you may replace that with the new name and restart iscsi service.

Then check at compellent the hba for the host IP equivalent, it should display the new name and the IP and you can now map the volume to the said server. 

sftp chroot environment - config and script

sftp chroot environment


Of course, sshd service should be enabled.


Sample Config:

#/etc/ssh/sshd_config
#
Protocol 2
SyslogFacility AUTHPRIV
MaxAuthTries 5
PubkeyAuthentication yes
PasswordAuthentication yes
ChallengeResponseAuthentication no
GSSAPIAuthentication yes
GSSAPICleanupCredentials yes
UsePAM yes
AcceptEnv LANG LC_CTYPE LC_NUMERIC LC_TIME LC_COLLATE LC_MONETARY LC_MESSAGES
AcceptEnv LC_PAPER LC_NAME LC_ADDRESS LC_TELEPHONE LC_MEASUREMENT
AcceptEnv LC_IDENTIFICATION LC_ALL
X11Forwarding yes
Ciphers aes128-ctr,aes192-ctr,aes256-ctr
MACs hmac-sha1,hmac-ripemd160
Subsystem       sftp    internal-sftp

Match Group sftpusers
        ChrootDirectory /home/sftpusers/%u
        ForceCommand internal-sftp
###EOF



Sample sftp adduser script

#/usr/local/sbin/sftp_adduser.sh
#!/bin/bash
#
if [ $# -lt 1 ]; then
        echo "Usage: `basename $0`" sftp username
        echo "   eg. `basename $0` "sftp_something
        exit 1
fi
RANPASS=`date +%s | sha256sum | base64 | head -c 8 ; echo`
USERNAME=$1
SFTPHOME=/home/sftpusers
SFTPSHELL=/usr/libexec/openssh/sftp-server

id $USERNAME 2> /dev/null 1>  /dev/null
if [ $? -eq 0 ]
then
echo "$1 already exist, please try again using another name"
exit

else
        useradd -d $SFTPHOME/$USERNAME -s $SFTPSHELL -p $RANPASS $USERNAME
        echo $RANPASS > /tmp/passwd.tmp001
        sleep 1
        passwd --stdin < /tmp/passwd.tmp001 $USERNAME
        echo $USERNAME >> /etc/listfile/sshusers

        #set permission
        usermod -g sftpusers $USERNAME
        usermod -G sftpusers $USERNAME

        #This is the default folder for sftp users
        mkdir $SFTPHOME/$USERNAME/FILES
     
        #set permissions
        chown root $SFTPHOME/$USERNAME
        chmod go-w $SFTPHOME/$USERNAME
        chown $USERNAME:sftpusers $SFTPHOME/$USERNAME/FILES
        chmod u+rwX $SFTPHOME/$USERNAME
        chmod 755 $SFTPHOME/$USERNAME
        chmod g+rx $SFTPHOME/$USERNAME

        #Details
        echo USER CREATED: $USERNAME
        echo USER DEFINED PASSWORD: $RANPASS
        echo FTP INTERNAL IP: 1.2.3.4
        echo FTP EXTERNAL IP: 111.222.112.221
        echo USER: $USERNAME has been created at ftp 1.2.3.4 | mail -s "created at sftp 1.2.3.4 on `date`" admin@myhost.com
fi

haproxy sample config used on actual setup

OS : Centos 6.8
Real Servers are CentOS 5.11



#---------------------------------------------------------------------
# Example configuration for a possible web application.  See the
# full configuration options online.
#
#   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    #
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     600                                                                                                                                           user        haproxy                                                                                                                                         group       haproxy                                                                                                                                       daemon                                                                                                                                                  
                                                                                                                                                           
    # turn on stats unix socket                                                                                                                             stats socket /var/lib/haproxy/stats                                                                                                                 tune.ssl.default-dh-param 2048                                                                                                                          
#---------------------------------------------------------------------                                                         # common defaults that all the 'listen' and 'backend' sections will                                                                   # use if not designated in their block                                                                                                            
#---------------------------------------------------------------------                                                                                      
defaults                                                                                                                                                    
   mode                    http                                                                                                                            
   log                     global                                                                                                                          
   option tcplog                                                                                                                                          
   option                  dontlognull                                                                                                                  
   option forwardfor       except 127.0.0.0/8                                                                                                              
    option http-server-close                                                                                                                                
    option abortonclose                                                                                                                                    
    option                  redispatch                                                                                                                      
    retries                 3                                                                                                                              
    ####
    timeout http-request    60s
    timeout queue           60m
    timeout connect         300s #s
    timeout client          60m
    timeout server          60m
    timeout http-keep-alive 5s
    timeout check           10s

    #tune.ssl.default-dh-param 2048

frontend WEBAPPS_HTTP
        bind 10.1.1.12:80
        reqadd X-Forwarded-Proto:\ http
        default_backend WEBAPPS_Backend
       
frontend WEBAPPS_HTTPS
        bind 10.1.1.12:443 ssl crt /etc/ssl/webapps.pem force-tlsv12
        reqadd X-Forwarded-Proto:\ https
        default_backend WEBAPPS_Backend

backend WEBAPPS_Backend
        balance roundrobin
        stick-table type ip size 1m expire 1h
        stick on src
        option abortonclose
        option forwardfor except 127.0.0.0/8
        option http-server-close
        option httpchk HEAD /check.html HTTP/1.0
        server WEB_10.1.1.13 10.1.1.13:80 weight 20 minconn 80 maxconn 180 inter 5s check
        server WEB_10.1.1.14 10.1.1.14:80 weight 20 minconn 80 maxconn 180 inter 5s check
        server WEB_10.1.1.16 10.1.1.16:80 weight 20 minconn 80 maxconn 160 inter 5s check

listen  stats 10.1.1.12:8443
        mode            http
        log             global

        maxconn 10

        timeout client     100s
        timeout server     100s
        timeout connect    100s
        timeout queue   100s

        stats enable
        stats hide-version
        stats refresh 10s
        stats show-node
        stats scope WEBAPPS_Backend
        stats auth hauser:Passworditsuptoyou
        stats uri /stats
        stats admin if TRUE

could not create unique index "pg_statistic_relid_att_inh_index" on postgresql 9.3 - FIXED

I have a newly restored database via symantec backup but got a problem.

Scenario:

• Postgresql version: 9.3 - Centos
• Able to access the database, via command line and pgadmin4 only, pgadmin 3 does not work.
• When creating any table, I cannot create, it will not proceed.
• When doing command reindex system DBNAME, i am stuck with the error below.
  
  DBNAME=# reindex system DBNAME;
  NOTICE: table "pg_catalog.pg_class" was reindexed
  ERROR: could not create unique index "pg_statistic_relid_att_inh_index"
  DETAIL: Key (starelid, staattnum, stainherit)=(2610, 15, f) is duplicated.
  
  LOG:
  Apr 26 14:50:44 stationname postgres[5452]: [10-2] 2017-04-26 14:50:44 PHT postgres DBNAME 127.0.0.1 DETAIL: Key (starelid, staattnum, stainherit)=(2610, 15, f) is duplicated.
  
  
  Got a tip from one blog I found to check if there were really a duplicate by sql command below: (link of blog: http://www.pateldenish.com/2016/09/recovering-corrupted-postgres-database.html)
  
  select starelid, staattnum, stainherit from pg_catalog.pg_statistic where starelid=2610 order by 2;
  
  
  DBNAME=# select starelid, staattnum, stainherit from pg_catalog.pg_statistic where starelid=2610 order by 2;
  starelid | staattnum | stainherit
  ----------+-----------+------------
  2610 | 1 | f
  2610 | 2 | f
  2610 | 3 | f
  2610 | 4 | f
  2610 | 5 | f
  2610 | 6 | f
  2610 | 7 | f
  2610 | 8 | f
  2610 | 9 | f
  2610 | 10 | f
  2610 | 11 | f
  2610 | 12 | f
  2610 | 13 | f
  2610 | 14 | f
  2610 | 15 | f
  2610 | 16 | f
  2610 | 17 | f
  2610 | 18 | f
  (18 rows)
  
  
  but there wee no duplicates found.
  
  So since I dont have a choice because I cannot seem to create any additional table, then I may need to restore another one from backup, and since I still have a running backup, so before doing another restore, I tried deleting the reported duplicate by command below:
  
  DBNAME=# delete from pg_catalog.pg_statistic where starelid=2610 and staattnum=15;
  DELETE 1
  
  DBNAME=# reindex database DBNAME;
  NOTICE: table "pg_catalog.pg_class" was reindexed
  ERROR: could not create unique index "pg_statistic_relid_att_inh_index"
  DETAIL: Key (starelid, staattnum, stainherit)=(2610, 13, f) is duplicated.
  
  DBNAME=# delete from pg_catalog.pg_statistic where starelid=2610 and staattnum=13;
  DELETE 1
  
  
  After the last deletion of duplicate, I able to reindex system and reindex database and seems those indexes were recreated and my issue on creating any table was fixed.
  
  
  
  

Small bash script to create or reset nginx reverse proxy protected password

Create a file /etc/nginx/listofuser

say it contains username

user1
user2
user3

Then, create a hidden file /etc/nginx/.users


Create a bash script and put it at cron, say daily so once run, it will reset the proxy password to daily and email the result to users on the list with CC on admin.


##############################################################################
#!/bin/bash


for USERNAME in `cat /etc/nginx/listofusers`
do
#Random Pass generation using openssl
RANPASS=`openssl rand -base64 32 | sha256sum | base64 | head -c 8 ; echo`

#htpasswd command
/usr/bin/htpasswd -b /etc/nginx/.users $USERNAME $RANPASS

#email the results to users
echo -e "USER: $USERNAME \nPASSWORD: $RANPASS \n \nPlease login at https://myproxy.example.com \n \nNote: Please expect email notification that it has been changed at the end of the day" | mail -s "password changed at myproxy on `date`" -r noreply@example.com $USERNAME@example.com,admin@example.com

done
###############################################################################

or if you want to load it at command line only and output the user and pass and check the file  /etc/nginx/.users, see below


#################################################################
#!/bin/bash
for USERNAME in `cat /etc/nginx/listofusers`
do

#Random pass generation using openssl (found it by googling)
RANPASS=`openssl rand -base64 32 | sha256sum | base64 | head -c 8 ; echo`

#htpasswd command
/usr/bin/htpasswd -b /etc/nginx/.users $USERNAME $RANPASS

#display the output after execution
echo "USER: $USERNAME"
echo "PASSWORD: $RANPASS"

done
#################################################################

[root@localhost ~]# /usr/local/bin/test.sh
Updating password for user user1
USER: user1
PASSWORD: ZTkxMTQ0
Updating password for user user2
USER: user2
PASSWORD: ZDc4MTMz
Updating password for user user3
USER: user3
PASSWORD: NTIwOGUx

Below are encrypted password file per htpasswd default encryption (CRYPT)

[root@localhost ~]# cat /etc/nginx/.users
user1:$apr1$HJ7/Q/qG$UTO3KujLj3rkANcNpHked1
user2:$apr1$uk59ZMD4$c4zjvMXarVK9zI7nKthz60
user3:$apr1$iIOsYmbh$GdKdkMjwfi4VEHfwTnER60

Below is the portion where you will set on your nginx config under location parameter

    auth_basic "Protected";
    auth_basic_user_file /etc/nginx/.users;

How to import multiple filters of a zimbra users.

To extract the filter of a single user at zimbra cli.


User the command below: example user is testsharedmbox@example.com
as zimbra user at cli, execute:

zmprov ga testsharedmbox@example.com zimbraMailSieveScript

Output below:


# name testsharedmbox@example.com
zimbraMailSieveScript: require ["fileinto", "reject", "tag", "flag"];

# Redirect
if anyof (header :matches ["subject"] "*") {
    redirect "user1@yahoo.com";
    redirect "user2@gmail.com";
    redirect "user3@linux.com";
    keep;
    stop;
}

So just imagine if you have 1000 redirect, so just use a little bash. Convert the above output to become like below as bash executable.

####

zmprov ma testsharedmbox@example.com zimbraMailSieveScript 'require ["fileinto", "reject", "tag", "flag"];

# Redirect
if anyof (header :matches ["subject"] "*") {
    redirect "user1@yahoo.com";
    redirect "user2@gmail.com";
    redirect "user3@linux.com";
    keep;
    stop;
}'

####