Its always a challenging task when moving from one version to another version on new Server. My presentation below is to upgrade the Old version of PostgreSQL 8.3 to PostgreSQL 9.0.4 on NEW SERVER. Basically, steps are very simple for upgradation, but need to take some extra care when bouncing the new server before and after restoration. Latest PostgreSQL, has lot of fixes in it, so it is recommended to use the new binaries for entire upgradation process.
A major milestone in PostgreSQL 9.0 is Streaming Replication(including DDL). Many of you all used configuring SR on Linux, but I would be presenting SR on Windows Platform. PostgreSQL wiki is the best guide for setting up the Streaming Replication.
For setting up SR on Windows, I would recommend to follow the PostgreSQL wiki steps with minor changes what needed for Windows Platform. I would like to show only the changes what you have to look for on Windows Platform in my blog.
In PostgreSQL, Backup & Recovery are very user friendly comparing with other database. Many of them won’t agree to this, ok lets not get into debate. Coming to Backups, PostgreSQL does’nt support INCREMENTAL BACKUP, however there are very consistant backup tools and OS level work-around to achieve this goal.
My pictorial presentation on PostgreSQL Backup and Recovery gives a complete conceptial idea. Looking into Diagram you can make out which backups can be used to restore or recover.
Above error thrown, when I was trying to create language plperlu. It indicates that there is a missing library libperl.so. Check out the steps performed to overcome this issue and successfully creating the plperlu language in PostgreSQL.
Method 1 (Finding the libperl.so and making softlink to that location)
postgres=# create LANGUAGE plperlu; ERROR: could not load library "/opt/PostgreSQL/9.0/lib/postgresql/plperl.so": libperl.so: cannot open shared object file: No such file or directory postgres=# \q Now, search for the file libperl.
In PostgreSQL, every table is an object, using pg_relation_size(‘object_name’) will give the size of the object. If you send the partition table in the place of ‘object_name’, it gives only that object size but not the sizes of child tables.
Check out the example given below.
postgres=# \dt+ List of relations Schema | Name | Type | Owner | Size | Description --------+---------------+-------+----------+------------+------------- public | child1 | table | postgres | 8192 bytes | public | child2 | table | postgres | 8192 bytes | public | parent | table | postgres | 0 bytes | (3 rows) pg_relation_size() on parent table will not give the exact size.