While performing PITR, would it be possible to Pause/Resume in PostgreSQL ?

Yes, truly possible and handled smartly by PostgreSQL. To demo this, first I need to take after the standard technique of Point in Time Recovery in PostgreSQL. Various Books/Articles/Blogs demoed extremely well by extraordinary authors, hence am not going into details of how to do it, however, heading off directly to the subject i.e., how to pause while recovering with same technique. Arguably, I put forth a mathematical expression out of PITR as “PITR = (Last Filesystem Backup(LFB) + WAL Archives generated after LFB + Un-Archived WAL’s in current $PGDATA/pg_xlogs)“.

Continue reading →

How to change all objects ownership in a particular schema in PostgreSQL ?

Few suggesion’s here (Thanks), inspired me to compose a bash script for changing all object’s (TABLES / SEQUENCES / VIEWS / FUNCTIONS / AGGREGATES / TYPES) ownership in a particular schema in one go. No special code included in a script, I basically picked the technique suggested and simplified the implementation method via script. Actually, REASSIGN OWNED BY command does most of the work smoothly, however, it changes database-wide objects ownership regardless of any schema.

Continue reading →

PostgreSQL Compressed Archive Logs in Windows

Many have blogged and offered glaringly on how to compress archive logs in PostgreSQL, I desire to share one of a short version from my end which’s on Windows. If archive storage is concern, then you can choose compressed archive logging feature in PostgreSQL. “archive_command(string)” in $PGDATA/postgresql.conf, is like a shell command to execute what’s passed in string section to copy the completed source file (WAL file segment in $PGDATA/pg_xlog) to destination(ARCHIVE LOCATION).

Continue reading →

ERROR: could not access file "$libdir/plpython2" - ERROR: could not access file "$libdir/plpython3"

Above error described on PG mailing as it failes to CREATE LANGUAGE plpython2u/plpython3u on PG9.3Beta. Error: postgres=# create language plpython3u; ERROR: could not access file "$libdir/plpython3": No such file or directory postgres=# create language plpython2u; ERROR: could not access file "$libdir/plpython2": No such file or directory Before doing some study on above errors I read below PG documentation link on how PostgreSQL permits to create langage plpython and how they should be configured.

Continue reading →

Improvements in Slony-I 2.2.0beta

Thanks to Slony-I team for releasing Slony-I 2.2.0Beta with many enhancements on key areas. Few are mentioned here: Event capturing protocol changed for performance (sl_log_1 / sl_log_2): Earlier release, any DML(INSERT/UPDATE/DELETE) event data must store as SQL statement in two tables(sl_log_1/sl_log_2). Now, the protocol has completely modified to COPY format, as a result of this there’ll be sensible performance improvement like lower processing overhead, lower memory consumption, less query processing work on subscriber database in slon process.

Continue reading →