In the database world, it’s fair to ask question to database that when have you started? In Oracle world, it can be easily find out by querying system level view V$INSTANCE. In PostgreSQL world, until today, I wasn’t able to answer that question without checking database log files. Today, I asked this question to OmniTi’s database operations irc channel and got answer in 20 minutes. I regret that I should have asked it earlier but anyways, now I know that in PostgreSQL it’s possible using this query.
omniti=# SELECT (pg_stat_file( 'postmaster.pid')).modification;
If someone complains that database was restarted , it would be easy as DBA to query and verify it. I hope this query will be helpful to you during day-to-day operations!
Over-allocated space in tables and indexes is a very common problem in PostgreSQL database where explosive data growth and changes occurs. This over allocated space is called “bloat”. Bloated tables and indexes waste resources of the system and cause large performance anomaly to the applications. Vacuum process helps to avoid bloat but some cases where even vacuum can not able to recover space for reuse. To shrink tables in such cases, DBA needs special attention and tools to remove bloat from the tables and indexes.
At PgEast 2011, I will discuss PostgreSQL MVCC design (compare with Oracle and MySQL) , ways to avoid bloat and online & offline methods to remove bloat from the tables and indexes. P90X your database talk will help you to keep databases skinnier and faster. If you attending this conference, looking forward to hear your experiences and tools that helps to avoid and remove bloat.
PostgreSQL is world best open source database. PostgrSQL is RDBMS database like Oracle . The main advantage of PostgreSQL is that it’s open source and free. Moreover, PostgreSQL provides comparable features like Oracle. Today, I came across an useful PostgreSQL Database Administration commands series. I would highly recommend following articles for the beginners to get hands on experience quickly in the PostgreSQL database administration.
Here are the links:
I hope these articles will help you to start your career in PostgreSQL database administration.
Many customers are asking for getting stats from their MySQL installation. It’s pretty easy to find stats per database from Com_ variables and graph them as counter to get the number of INSERT/UPDATE/DELETE on the database level. To get the DML stats from MySQL at table level there is only one option : mysqlbinlog . Here is the best post I found that can help you to answer your question.
Get the MySQL table stats now!
Oracle’s easy to create and manage partition table feature is the best selling point for them to attract wide range of applications to use partitioning features easily . Oracle supports partitioning since Oracle8i but there were limited features in the first release. In Oracle8i, they supported only RANGE and HASH partitions management.
On the other hand, PostgreSQL support partitioning
but it’s cumbersome to create and manage partitioned tables. At least to start with, PostgreSQL should implement commands to make RANGE partition creation and management easy for the users. .
This is an example for creating range partitioned tables in Oracle …
CREATE TABLE users
( user_id NUMBER NOT NULL,
registration_date DATE NOT NULL,
PARTITION BY RANGE (registration_date)
(PARTITION users_q1 VALUES LESS THAN (TO_DATE('01/04/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q2 VALUES LESS THAN (TO_DATE('01/07/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q3 VALUES LESS THAN (TO_DATE('01/09/2011', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION users_q4 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')) TABLESPACE users);
When will I be able to CREATE range partition table and manage with ALTER TABLE commands in PostgreSQL?
I have been using PITR master-slave solution for long time for “failover” to slave server when there is a problem with the master server. It has proven a valuable solution for most of clients needs. The problem with “failover” solution is that we have to rebuild old-master as slave from completely new base backup from newly promoted master server. Oracle provides nice feature called “Graceful Switchover“. In this method, you can switch duty between master and slave servers and don’t have to rebuild old-master as slave from newly promoted master server. It could be very useful during server or data center move as we can shutdown old database and can have total control over xlogs to be processed before we start traffic on newly promoted master database.
I couldn’t find any graceful switchover document or blog for the PostgreSQL . Today, I spent some time on testing same theory as Oracle uses for the switchover. Oracle controls switchover using controlfile, Why can’t I use pg_control to do the same in totally controlled environment?
For PostgreSQL graceful switchover, I tested following scenario on very small test database server with pre configured setup of master and PITR slave database:
- Step-1 : Shutdown current master database
- Step-2 : Apply all wal logs including the one created during shutdown process to slave database server
- Step-3: Promote slave as master in read/write mode using triggered failover file
- Step-4: Before pointing production traffic to newly promoted master server, take backup of $PGDATA/global/pg_control and $PGDATA/global/pgstat.stat files. These two files differ between master and slave after failing over slave database into production mode.
- Step-5: Setup old – master server for slave for newly promoted master sever and replace $PGDATA/global/pg_control and $PGDATA/global/pgstat.stat with the files backed up in Step-4
- Step-6: Check log files on old-master to make sure that it’s recovering copied wal logs from newly promoted master server(old-slave server)
Yay! I could successfully switched over slave to master and vice versa !
At this stage, old-master(new slave) started playing wal logs coming from new-master(old-slave) server. After that, I failed over new slave server to make sure we can actually bring the database in production mode. It worked like a charm!! I ran database wide vacuum after failover and it ran fine without any error.
I have done limited testing as I have tested this with very tiny setup of “pagila” database on test server and I don’t recommend you to use on the “production database” servers as of now.
Please test this in your test or dev environment and let me know your results and concerns!!