Category Archives: SQL

SQL Servers

Barman

# install barman
apt-get install barman

# generate ssh keys for user barman
sudo -u barman -i
ssh-keygen
cd ~/.ssh/
cp id_rsa.pub authorized_keys
# cat id_rsa.pub and copy it
exit

# generate ssh keys for user postgres
sudo -u postgres -i
ssh-keygen
cd ~/.ssh/
cp id_rsa.pub authorized_keys
# put barman's id_rsa.pub into postgres's authorized_keys
# cat postgres'sid_rsa.pub and copy it
exit

sudo -u barman -i
# put id_rsa.pub postgres's id_rsa.pub into barman's authorized_keys
exit

add config of your postgresql server into /etc/barman.conf:

1
2
3
4
[srv1]
description = "srv1 pgsql"
ssh_command = ssh postgres@127.0.0.1
conninfo = host=127.0.0.1 user=postgres

where srv1 — name of server

barman check
barman status srv1
barman backup srv1
barman cron
man barman

Postgresql: export/import users with privileges

It's easy to transfer users from one instance to anothe:
#dump users and permissions
sudo -u postgres pg_dumpall -g > globals_only.dump
#restore them
cat globals_only.dump | sudo -u postgres psql
# or if you would like to choose specific user
grep USERNAME globals_only.dump

1
2
3
CREATE ROLE "USERNAME";
ALTER ROLE "USERNAME" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md56933b22c7ea06924799dbac5d7ed8072';
GRANT ro TO "USERNAME" GRANTED BY postgres;

These commands you could input directly to postgres command-line.

Migrate from mysql to percona

1
2
gpg --keyserver hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg -a --export CD2EFD2A | sudo apt-key add -

add to /etc/apt/sources.list.d/percona.list

1
2
deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main

where VERSION is a name of your ubuntu version,
you can get it by command

1
lsb_release -a |grep name |awk '{print $2}'

backup data

1
2
cp -Rf /var/lib/mysql /var/lib/mysql-old
cp /etc/my.cnf /etc/my.cnf-old

Install percona (it automatically removes mysql and converts databases)

1
2
apt-get update
apt-get install percona-server-server-5.5 percona-server-client-5.5

Part of this article was taken from http://planet.jboss.org/post/drop_in_migration_from_mysql_to_percona_db_in_two_minutes

IT Security Brothers (http://itsb.pro)

Hi guys, I would like to present you our new project IT Security Brothers http://itsb.pro
We provide pentest, consultations and IT outsourcing services.
Feel free to hire us for IT jobs.

Postgresql 9.2 for Ubuntu 12.04 and 10.04

Add repository: http://www.postgresql.org/download/linux/ubuntu/

1
2
3
echo deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main | sudo tee /etc/apt/sources.list.d/postgresql.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

setup postgres:

1
sudo apt-get install postgresql-9.2 postgresql-contrib-9.2