Duplicating a copy of schema to another schema name on an existing postgresql database

Problem: Need to duplicate the content of say schema001 inside a DB name dbtest001 to a new schema with name schema999.


Specs: Postgres 8.2.17


Solution:

1. Login as root, su - postgres

2. create a backup first of the DB dbtest001, command below:
pg_dump -U postgres dbtest001 -f /tmp/dbtest001-backup.sql

3. psql dbtest001

4. We need to rename the schema001 by alter command:
SCHEMA schema001 RENAME TO schema999;

then control d to exit inside psql.

5. After renaming it to the new schema name, dump the database schema only for the particular schema.
pg_dump -U postgres dbtest001 --schema=schema999 --schema-only -f /tmp/schema999.sql

6. Rename back the schema999 to its original name - schema001.

psql dbtest001

SCHEMA schema999 RENAME TO schema001;

7. Now create schema999


CREATE SCHEMA schema999;

control d to exist again from psql console.

8. Now restore the dumped file on the database.
psql -U postgres -d dbtest001 < /tmp/schema999.sql


The schema999 should now been populated with the content of schema001.


-Ohbet


Edit:


01302012

There is a request to do this on a live system, so I cannot rename the schema, and for searching, some mentioned the dump the original schema to a file, rename the schema name on that file to a new name, here im using sed to rename then create the new schema on the DB and restore the dumped file.


1. Of course, create a backup first. - see 1 above


2. Dump the existing schema.
pg_dump -U postgres dbtest001 --schema=schema001 --schema-only -f /tmp/schema001.sql

3. Rename the schema001 entry on that file and dump in on another file named schema999.
sed 's\schema001\schema999\g' /tmp/schema001.sql > /tmp/schema999.sql

4.  psql dbtest001

5. Create the new schema.

CREATE SCHEMA schema999;

6. Dump the edited schema named schema999.sql

psql -U postgres -d dbtest001 < /tmp/schema999.sql


Note: This is the same result as doing the above, the advantage is you dont need to rename the schema, since its a live system.






 

Fedora 16 boot problem

Problem: When I accidentally hit the recovery boot process of HP540 Laptop, fedora 16 grub menu wiped out. When restarted and cancelled the Recovery process, it will directly boot to XP, no grub menu.

Setup: Dual boot with Windows XP.

Solution:

I have still the copy of Fedora 16 re-imaged on my USB, I boot the laptop using the USB and make the repair from there.

1. Open terminal shell.
2. Do fdisk -l to find out what disk does the laptop has, on mine, its /dev/sda as the main disk.
3. Find the /boot partition, mine is at /dev/sda3 since I created a separate mount for it
4. mount /dev/sda3 /boot - this will mount your actual /boot on the live system.
5. run the command grub2-install /dev/sda
6. reboot, on mine, it successfully restored the grub and able to get back to my fedora 16 system.

Creating master user for Dovecot

This is based on the the dovecot wiki - http://wiki.dovecot.org/Authentication/MasterUsers

The ff: will be added or appended at /etc/dovecot and for this, am using dovecot-1.0.7-2.el5


#
auth_master_user_separator=*
#
auth default {
#
 passdb passwd-file {
        args = /etc/dovecot/passwd.masterusers
        master = yes
        }
#


and the entry for  /etc/dovecot/passwd.masterusers contains the ff: 

#/etc/dovecot/passwd.masterusers
 
master:{plain}mypassword
#

So testing if it works, telnet the server.

telnet servertest.com 143


[user@servertest ~]$ telnet servertest.com 143
Trying servertest.com...
Connected to servertest.com.
Escape character is '^]'.
* OK Dovecot ready.
a login username*master mypassword
a OK Logged in.


When using imap client, the username that will be use is username*master.

ssh client login email alert

I just got it when searching via google, I forgot the link but thanks for this share.


The code below should be inserted at /etc/profile so that every time someone logon via ssh in your server, it will send an email alert, assuming that the server is capable of sending an email via command line which will not be tackled here.

################
if [ -n "$SSH_CLIENT" ]; then

   TEXT="$(date): ssh login by ${USER}@$(hostname -f)"

   TEXT="$TEXT from $(echo $SSH_CLIENT|awk '{print $1}')"

   echo $TEXT|mail -s "ssh login" linux.admin@mydomain.com

fi
################

Restricting ssh access with access.conf and listfile



Related files:

/etc/security/access.conf
/etc/pam.d/sshd

Below is my /etc/pam.d/sshd


#%PAM-1.0
auth required pam_listfile.so item=user sense=allow file=/etc/allowed_to_ssh_listfile onerr=fail
auth       required     pam_stack.so service=system-auth
auth       required     pam_nologin.so
account    required     pam_stack.so service=system-auth

#Comment

#account    required     pam_access.so - > this is to be added so that the
account    required     pam_access.so
password   required     pam_stack.so service=system-auth
session    required     pam_stack.so service=system-auth
session    required     pam_loginuid.so


#/etc/allowed_to_ssh_listfile - example content
root
user1
user2

Note: root has been included here but will be restricted on the /etc/security/access.conf


#/etc/security/access.conf
#The format of the ip on centos4 has .(dot) at the end
#for centos5, it can be no .(dot)
#the cron entry is to allow root to run cron, else cron will fail for root
+ : root : 10.10.9.3.
+ : root : 10.10.9.6.
+ : root : 127.0.0.1.
+ : root : 10.120.1.6.
+ : root : 127.0.0.1
+ : root : cron crond :0 tty1 tty2 tty3 tty4 tty5 tty6
+ : root : LOCAL
#
- : root : ALL

Load balancing HTTPD with Ldirectord

Requirement:
1 ldirectord load balancer - IP: 10.100.1.56

2 HTTPD Server
SERVER01 - IP: 10.100.1.59
SERVER02 - IP: 10.100.1.60
Virtual IP: 10.100.1.58

Linux Distro Used: CentOS 5.5

############Ldirectord setup########################

1. Ldirectord setup - Packages required: heartbeat heartbeat-ldirectord ipvsadm. Repository I used is Epel which as of this writing, heartbeat*-2.1.4. is the available version.

install command: yum install heartbeat heartbeat-ldirectord ipvsadm

2. Create a virtual interface for eth0 for file /etc/sysconfig/network-scripts/ifcfg-eth0:0

#/etc/sysconfig/network-scripts/ifcfg-eth0:0
DEVICE=eth0:0
BOOTPROTO=none
BROADCAST=10.100.255.255
HWADDR=00:14:AA:00:00:1F
IPADDR=10.100.1.58
NETMASK=255.255.0.0
NETWORK=10.100.0.0
ONBOOT=yes

3. Edit /etc/sysctl.conf

# Controls IP packet forwarding
net.ipv4.ip_forward = 1

4. Create /etc/ha.d/ldirectord.cf

#/etc/ha.d/ldirectord.cf
checktimeout=10
checkinterval=2
autoreload=no
logfile="/var/log/ldirectord.log"
quiescent=no
emailalert = "admin@mycompany.com"
virtual=10.100.1.58:80
real=10.100.1.59:80 gate 100
real=10.100.1.60:80 gate 1
service=http
request="check.html"
receive="check_dont_delete_me"
scheduler=wrr
protocol=tcp
checktype=negotiate
#
virtual=10.100.1.58:443
real=10.100.1.59:443 gate 100
real=10.100.1.60:443 gate 1
service=http
request="check.html"
receive="check_dont_delete_me"
scheduler=wrr
protocol=tcp
checktype=connect
#On the checktype for the https config
#I used hecktype=connect since negotiate will not work
#it wont be able to get the certificate.

#For the real server entry the 100 set the prio
# 1 set less priority

##########Real Server Setup#####################

1. Create a virtual loopback interface.

vim /etc/sysconfig/network-scripts/ifcfg-lo:0

DEVICE=lo:0
IPADDR=10.100.1.58
NETMASK=255.255.255.255
ONBOOT=yes
NAME=loopback

2. Edit /etc/sysctl.conf

#Append the ff below:

net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.eth0.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.eth0.arp_announce = 2

3. issue the command

Ifup eth0:0
sysctl -p

4. create a file check.html at real server
Document Root Dir.

echo “check_dont_delete_me” > /var/www/html/check.html

This should be accessible directly by ldirector for check purpose

5. Do the same on Real Server 2

#########Checking###########

Command to check: ipvsadm -L -n

CoovaChilli on Centos 5.5

Im installing CoovaChilli on Centos 5.5

the setup is we have an existing coovachilli that has been setup on ubuntu and authentication is already feed to freeradius and mysql as db backend.

Process:

1. Setup Centos to be able to compile to rpm, need to install rpm-build ang other pre-requisites.
2. Download coovachilli source, the one that has spec file as of this writing is coova 1.2.2, copy the spec file -- attached below:

#spec file

%define _unpackaged_files_terminate_build 0
%define _missing_doc_files_terminate_build 0
Summary: Coova-Chilli is a Wireless LAN Access Point Controller
Name: coova-chilli
Version: 1.2.4
Release: 1
URL: http://www.coova.org/
Source0: %{name}-%{version}.tar.gz
License: GPL
Group: System Environment/Daemons
BuildRoot: %{_tmppath}/%{name}-root

%description

Coova-Chilli is a fork of the ChilliSpot project - an open source captive
portal or wireless LAN access point controller. It supports web based login
(Universal Access Method, or UAM), standard for public HotSpots, and it
supports Wireless Protected Access (WPA), the standard for secure roamable
networks. Authentication, Authorization and Accounting (AAA) is handled by
your favorite radius server. Read more at http://www.coova.org/.

%prep
%setup

%build

%configure --enable-miniportal --enable-chilliredir --enable-chilliproxy --with-openssl

make

%install
make install DESTDIR=$RPM_BUILD_ROOT

rm -rf $RPM_BUILD_ROOT/usr/include/*
rm -rf $RPM_BUILD_ROOT/usr/lib/*.la
rm -rf $RPM_BUILD_ROOT/usr/lib/*.a

%clean
rm -rf $RPM_BUILD_ROOT
make clean

%post
/sbin/chkconfig --add chilli

%preun
if [ $1 = 0 ]; then
/sbin/service chilli stop > /dev/null 2>&1
/sbin/chkconfig --del chilli
fi

%files
%defattr(-,root,root)
%{_sbindir}/*
%{_libdir}/*.so*
%{_libdir}/python/CoovaChilliLib.py
%{_sysconfdir}/init.d/chilli
%doc AUTHORS COPYING ChangeLog INSTALL README doc/dictionary.chillispot doc/hotspotlogin.cgi
%config %{_sysconfdir}/chilli.conf
%config %{_sysconfdir}/chilli/gui-config-default.ini
%config(noreplace) %{_sysconfdir}/chilli/defaults
%dir %{_sysconfdir}/chilli
%dir %{_sysconfdir}/chilli/www
%attr(755,root,root)%{_sysconfdir}/chilli/www/config.sh
%{_sysconfdir}/chilli/www/*
%{_sysconfdir}/chilli/wwwsh
%{_sysconfdir}/chilli/functions
%{_sysconfdir}/chilli/*.sh
%{_mandir}/man1/*.1*
%{_mandir}/man5/*.5*
%{_mandir}/man8/*.8*

%changelog
* Sat Jan 2 2010
- 1.2.0 release
* Thu Sep 30 2007
- 1.0.8 release
* Thu Aug 20 2007
- 1.0-coova.7 release
* Thu Jun 7 2007
- 1.0-coova.6 release
* Wed May 16 2007
- 1.0-coova.5 release
* Wed Feb 07 2007
- 1.0-coova.4 release
* Wed Nov 15 2006
- 1.0-coova.3 release
* Thu Mar 25 2004
- Initial release.


The first 2 line above has just been added, because there are error when compiling the ver 1.2.4.

Download 1.2.4 from this link:

wget http://ap.coova.org/chilli/coova-chilli-1.2.4.tar.gz


recompile using rpmbuild.

1. copy first the souce to /usr/src/redhat/SOURCES
2. rpmbuild -bb coova.spec

once finished, install the compiles rpm file

hereś my default config
/etc/chilli.conf


#
# Chilli Configuration
#
# To configure chilli, see /etc/chilli/default
# and then create your own /etc/chilli/config
# start / stop chilli with /chilli
#
# For help with coova-chilli,
# visit http://coova.org/wiki/index.php/CoovaChilli
#
# This file can either contain all your chilli configurations, or include
# other files, as shown per default below. The idea here is that main.conf
# contains your main configurations, hs.conf is for your configurations
# delivered by RADIUS (using the Administrative-User login), and local.conf
# is for any other settings you might configure by hand for this particular
# installation.

include /etc/chilli/main.conf
include /etc/chilli/hs.conf
include /etc/chilli/local.conf

ipup=/etc/chilli/up.sh
ipdown=/etc/chilli/down.sh


and
/etc/chilli/defaults

HS_WANIF=eth0 # WAN Interface toward the Internet
HS_LANIF=eth1 # Subscriber Interface for client devices
HS_NETWORK=192.168.182.0 # HotSpot Network (must include HS_UAMLISTEN)
HS_NETWORK=192.168.182.1
HS_NETMASK=255.255.255.0 # HotSpot Network Netmask
HS_UAMLISTEN=192.168.182.1 # HotSpot IP Address (on subscriber network)
HS_UAMPORT=3990 # HotSpot UAM Port (on subscriber network)
HS_UAMUIPORT=4990 # HotSpot UAM "UI" Port (on subscriber network, for embedded portal)
HS_DNS1=10.10.1.100
HS_DNS2=10.10.1.160
HS_NASID=nas01
HS_RADIUS2=127.0.0.1
HS_RADIUS=10.101.1.231
HS_UAMALLOW=https://192.168.182.1/cgi-bin/hotspotlogin.cgi
HS_RADSECRET=easyhotspot # Set to be your RADIUS shared secret
HS_UAMSECRET=easyhotspot # Set to be your UAM secret
HS_UAMALIASNAME=chilli
HS_UAMSERVER=$HS_UAMLISTEN
HS_UAMFORMAT=http://\$HS_UAMLISTEN:\$HS_UAMUIPORT/www/login.chi
HS_UAMHOMEPAGE=http://\$HS_UAMLISTEN:\$HS_UAMPORT/www/coova.html
HS_UAMSERVICE=https://192.168.182.1/cgi-bin/hotspotlogin.cgi
HS_RADCONF_SERVER=10.101.1.231 # RADIUS Server
HS_RADCONF_SECRET=easyhotspot # RADIUS Shared Secret
#this should also be coincide on
#the remote radius server
HS_RADCONF_AUTHPORT=1812 # Auth port
HS_TCP_PORTS="80 443"
HS_MODE=hotspot
HS_TYPE=chillispot
HS_RADAUTH=1812
HS_RADACCT=1813
HS_WWWDIR=/etc/chilli/www
HS_WWWBIN=/etc/chilli/wwwsh
HS_PROVIDER=MYCOMPANY
HS_PROVIDER_LINK=http://www.mycompany.com
HS_LOC_NAME="CONCENTRIX HOTSPOT" # WISPr Location Name and used in portal


Edit up.sh - source: https://help.ubuntu.com/community/WifiDocs/CoovaChilli


# may not have been populated the first time; run again
[ -e "/var/run/chilli.iptables" ] && sh /var/run/chilli.iptables 2>/dev/null
# force-add the final rule necessary to fix routing tables
iptables -I POSTROUTING -t nat -o $HS_WANIF -j MASQUERADE



Install httpd server

Copy /usr/share/doc/coova-chilli-1.2.4/hotspotlogin.cgi to /var/www/cgi-bin

mv /usr/share/doc/coova-chilli-1.2.4/hotspotlogin.cgi /var/www/cgi-bin


edit


/var/www/cgi-bin/hotspotlogin.cgi


look for $uamsecret and set it to coincide with HS_UAMSECRET=easyhotspot set at /etc/chilli/defaults

$uamsecret = "easyhotspot";


-
starting chilli

/etc/init.d.chilli start

to be cont...