Tag Archives: postgres

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
... ... ... ...

Postgresql: pg_dump tables with dot in names

if this variant of command doesn't work:
sudo -u postgres pg_dump -t «gm.shard» --no-owner gmtool  > gm.shard.pgsql

try to make this variant:

sudo -u postgres pg_dump -t «gm?shard» --no-owner gmtool  > gm.shard.pgsql

you should understand that for this variant table gmsshard will be right too

or

sudo -u postgres pg_dump -t «gm*shard» --no-owner gmtool  > gm.shard.pgsql

but for the last variant gm.test.killMob.shard — will be right too.