PostgreSQL Architecture

Based on PostgreSQL documentation and various articles, I comprehend the PostgreSQL Architecture as below:
PostgreSQL instance consists of set of Process and Memory. PostgreSQL uses a simple “process per-user” client/server model. The major processes are:

The ‘postmaster’ which is:

supervisory daemon process,
‘postmaster’ is attached to shmmem segment but refrains from […]

read more

Install and Configure pgBadger for PostgreSql 9.x on CentOS

Yes, one of the most important things is knowing how the databases are used. Indeed if we know what are the slowest queries, the most frequent queries, etc.; we can optimize the databases (by adding an index, changing a query or modifying a schema/structure) and thus improve the performance! To […]

read more

Query Optimization Techniques

Below Query Optimization Techniques should be taken care of to optimize SQL queries:

Use Index
Aggregate table – Pre-populating tables at higher levels so less amount of data need to be parsed.
Vertical partitioning
Horizontal Partitioning
Denormalization
Server Tuning
Move Queries to stored procedures
Remove unneeded views
Only query what you really need
Sort only when required
You should do […]

read more

After server migration only the homepage works

On Ubuntu server, if after server migration only home page works; you can follow below troubleshooting to fix the issue:

In /etc/apache2/apache2.conf, edit AllowOverride None for /var/www/ to AllowOverride All

Type in the following command to enable mod_rewrite for .htaccess

Restart your apache server:

The problem will be solved!!!

If you have access to […]

read more