Tag Archives: postgres

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.

Install postgresql 9.2

Download version for your platform:
[spoiler title="Platforms packages"]
Fedora 17 — x86_64
http://yum.pgrpms.org/9.2/fedora/fedora-17-x86_64/pgdg-fedora92-9.2-5.noarch.rpm
Fedora 16 — i686
http://yum.pgrpms.org/9.2/fedora/fedora-16-i386/pgdg-fedora92-9.2-5.noarch.rpm
Fedora 16 — x86_64
http://yum.pgrpms.org/9.2/fedora/fedora-16-x86_64/pgdg-fedora92-9.2-5.noarch.rpm
Fedora 15 — i686
http://yum.pgrpms.org/9.2/fedora/fedora-15-i386/pgdg-fedora92-9.2-5.noarch.rpm
Fedora 15 — x86_64
http://yum.pgrpms.org/9.2/fedora/fedora-15-x86_64/pgdg-fedora92-9.2-5.noarch.rpm
Red Hat Enterprise Linux 6 — i386
http://yum.pgrpms.org/9.2/redhat/rhel-6-i386/pgdg-redhat92-9.2-7.noarch.rpm
Red Hat Enterprise Linux 6 — x86_64
http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
Scientific Linux 6 — i386
http://yum.pgrpms.org/9.2/redhat/rhel-6-i386/pgdg-sl92-9.2-8.noarch.rpm
Scientific Linux 6 — x86_64
http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-sl92-9.2-8.noarch.rpm
CentOS 6 — i386
http://yum.pgrpms.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm
CentOS 6 — x86_64
http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm
Red Hat Enterprise Linux 5 — i386
http://yum.pgrpms.org/9.2/redhat/rhel-5-i386/pgdg-redhat92-9.2-7.noarch.rpm
Red Hat Enterprise Linux 5 — x86_64
http://yum.pgrpms.org/9.2/redhat/rhel-5-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
Scientific Linux 5 — i386
http://yum.pgrpms.org/9.2/redhat/rhel-5-i386/pgdg-sl92-9.2-8.noarch.rpm
Scientific Linux 5 — x86_64
http://yum.pgrpms.org/9.2/redhat/rhel-5-x86_64/pgdg-sl92-9.2-8.noarch.rpm
CentOS 5 — i386
http://yum.pgrpms.org/9.2/redhat/rhel-5-i386/pgdg-centos92-9.2-6.noarch.rpm
CentOS 5 — x86_64
http://yum.pgrpms.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm
[/spoiler]

yum install pgdg-<platform>92-<full version>.noarch.rpm
add "exclude=postgresql* " (without quotes) to /etc/yum.repos.d/-Base.repo in the end of sections [base] and [updates].

yum install postgresql92 postgresql92-server postgresql92-contrib
#initialize db
service postgresql-9.2 initdb
service postgresql-9.2 start
chkconfig postgresql-9.2 on

Replication:
how to make replication you can learn from official wiki:
https://wiki.postgresql.org/wiki/Streaming_Replication