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.
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.
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.
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.
- 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)
Starting from PostgreSQL 8.4, there is a valuable addition in PostgreSQL features list: Per function statistics. Prior to this feature , there wasn’t an easy way to find out most time consuming functions in the database.
#track_functions = none # none, pl, all
Once you have track_functions enabled in your database, you can get details by querying pg_stat_user_functions view:
select * from pg_stat_user_functions;
If you’ve ever gotten a page about database connections are blocked, or phone call or email from an annoyed user whose transaction just won’t go through, or from a developer who can’t understand why application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked.
postgres=# insert into testlock values (‘lock’,'me’);
INSERT 0 1 postgres=# insert into testlock values (‘find’,'me’);
INSERT 0 1
postgres=# select * from testlock;
foo | bar
find | me
postgres=# update testlock set foo='hang' where foo='lock';
AndSession 2, try to update same row:
postgres=# update testlock set foo='escapeme' where foo='lock';
This statement will hang, blocked by the lock that Session 1 is holding on the row.
-[ RECORD 1 ]
locked_mode | ShareLock
blocked_pid | 9468
blocked_user | postgres
blocked_statement | update testlock set foo='escapeme' where foo='lock';
blocked_query_age | 00:10:28.98475
blocking_pid | 8056
blocking_user | postgres
blocking_statement | IDLE in transaction
blocking_query_age | 00:10:24.403767
blocking_xact_age | 00:10:58.529881
Let me know if you have suggestions to make it more better.
I found these lessons shared by someone! He learned from a 90 year old person … Good read!! 1. Life isn’t fair, but it’s still good. 31. Don’t audit life. Show up and make the most of it now. 32. Growing old beats the alternative — dying young. 33. Your children get only one childhood.
2. When in doubt, just take the next small step.
3. Life is too short to waste time hating anyone.
4. Your job won’t take care of you when you are sick. Your friends and parents will. Stay in touch.
5. Pay off your credit cards every month.
6. You don’t have to win every argument. Agree to disagree.
7. Cry with someone. It’s more healing than crying alone.
8. Save for retirement starting with your first paycheck.
9. When it comes to chocolate, resistance is futile.
10. Make peace with your past so it won’t screw up the present.
11. Don’t compare your life to others. You have no idea what their journey is all about.
12. If a relationship has to be a secret, you shouldn’t be in it..
13. Take a deep breath. It calms the mind..
14. Get rid of anything that isn’t useful, beautiful or joyful.
15. Whatever doesn’t kill you really does make you stronger.
16. It’s never too late to have a happy childhood. But the second one is up to you and no one else.
17. When it comes to going after what you love in life, don’t take no for an answer.
18. Burn the candles, use the nice sheets, wear the fancy lingerie. Don’t save it for a special occasion. Today is special.
19. Over-prepare, then go with the flow.
20. Be eccentric now. Don’t wait for old age to wear purple.
21.. The most important sex organ is the brain.
22. No one is in charge of your happiness but you.
23. Frame every so-called disaster with these words ”In five years, will this matter?”
24. Always choose life.
25. Forgive everyone everything.
26. What other people think of you is none of your business.
27. Time heals almost everything. Give time, time.
28. However good or bad a situation is, it will change.
29. Don’t take yourself so seriously. No one else does.
30. Believe in miracles.
34. All that truly matters in the end is that you loved.
35. Get outside every day. Miracles are waiting everywhere.
36. If we all threw our problems in a pile and saw everyone else’s, we’d grab ours back.
37. Envy is a waste of time. You already have all you need.
38. The best is yet to come.
39. No matter how you feel, get up, dress up and show up.
41. Life isn’t tied with a bow, but it’s still a gift.
I found these lessons shared by someone! He learned from a 90 year old person … Good read!!
1. Life isn’t fair, but it’s still good.
31. Don’t audit life. Show up and make the most of it now.
32. Growing old beats the alternative — dying young.
33. Your children get only one childhood.
RAID 10 = Combining features of RAID 0 + RAID 1. It provides optimization for fault tolerance. RAID 0 helps to increase performance by striping volume data across multiple disk drives. RAID 1 provides disk mirroring which duplicates your data.
In RAID5, 4th disk is used for parity bit.