Tag Archives: postgresql

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

Postgresql: change owner on all tables

1
2
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ;
do psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done
1
2
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ;
do psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done
1
2
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ;
do psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done taked from http://bit.ly/xOlrNV

Dovecot 2 + PostgreSQL

dovecot.conf

protocols = imap pop3
auth_mechanisms = PLAIN
log_timestamp = "%Y-%m-%d %H:%M:%S "
mail_location = maildir:/home/vmail/%d/%n/Maildir
shutdown_clients = yes

ssl = yes
ssl_cert = </etc/ssl/certs/dovecot.pem
ssl_key = </etc/ssl/private/dovecot.pem

disable_plaintext_auth = yes
auth_default_realm =  # there is maybe domain name.

login_greeting = Welcome to best email server. Continue reading Dovecot 2 + PostgreSQL

Postgresql: how to clean pg_xlog

sudo /etc/init.d/postgresql stop

du -sh /var/lib/postgresql/8.4/main/pg_xlog/
985M     /var/lib/postgresql/8.4/main/pg_xlog/

/usr/lib/postgresql/8.4/bin/pg_controldata /var/lib/postgresql/8.4/main/
...
Latest checkpoint's NextXID:          1/2718420992
Latest checkpoint's NextOID:          496696
...
sudo -u postgres /usr/lib/postgresql/8.4/bin/pg_resetxlog -o 496696 -x 2718420992 -f /var/lib/postgresql/8.4/main/

Transaction log reset

du -sh /var/lib/postgresql/8.4/main/pg_xlog/
17M     /var/lib/postgresql/8.4/main/pg_xlog/

sudo /etc/init.d/postgresql start

How to change owner for created tables/views/etc


Tables:

1
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

Sequences:

1
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

Views:

1
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done

postgresql: join analog

Example:

I have database «WAR» with two tables:

a_table

id guildId guildName
0 100500 RealGods
1 22115 BestPlayers
... ... ...

b_table where fields «attackerGuild» and «defenderGuild» are foreign keys from a_table.

id attackerGuild defenderGuild
0 1 0
1 5 6
2 ... ...

 

I want to make select and see guildId of attacker and defender.

select a."attackerGuild", b."guildId", a."defenderGuild", c."guildId" from «a_table» a, «b_table» b, «b_table» c where (a."attackerGuild" = b."id" and a."defenderGuild" = c."id");

Now i have table:

acctackerGuild guildId defenderGuild guildId
1 22115 0 100500
5 33421 6 895656
... ... ... ...