Günce Kaya

Hi, SQL Server announced Dynamic Data Masking feature not long ago and I'm Postgresql DBA! PostgreSQL does not have like that feature yet. So, what if we need data masking on our database's tables? If you need make strict your database tables and hide your column but not that all follow bellow function! This function shows only the first and last two characters. If you looking for some solution for your table's column and if it is the character you can create bellow function in your database and enjoy it! CREATE OR REPLACE FUNCTION dba.pg_datamask_column(col character varying)   RETURNS character varying AS $BODY$ begin  if (select length(col))<=2 then return col; elsif (select length(col))=3 or  (select length(col))=4  then return  (select replace(col,right(col,2),'xx')); else return (select  replace(substring(col from 1 for length(col)-2), right(substring(col from 1 for length(col)-2),length(col)-4),'xxxx')||right(col,2)); end if;

Hello, I'm gonna share my experience on partitioning and show how affect partition on scripts. I'm gonna show you how to partitioning on existing table. Let's begin to following steps; Create new table. It's gonna be partitioned table. Create partition tables. Grant privileges to partition tables and partitioned table. Generate function and trigger on partitioned table. Insert data from main table to partitioned table. Create indexes if you had on main table on partition tables. Check partition tables everything is fine. Rename main table. Rename partitioned table name like main table Drop old main table. Not that: if you have some indexes or constraint on your main table, not to increase inserting time you should create your new main table and other partition tables without constraints and indexes. After insert whole data to partition tables you can add indexes and constraints. It will be help you to executing insert statement more quickly.  Bef

Hi, If you can not reach pg_ctl command in bash also you are sure this command exists in your server, check $PATH. -bash-4.2$ echo $PATH /usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin So, the directory of pg_ctl is not seem in PATH. Input directory of pg_ctl and check it then run it.

Hello, I would like to share my knowledge about how to get basebackup in PostgreSQL and with getting lots of errors and how to overcome it. So you should be ready to getting crash and doing stupid things and to be achieve! First of all, you need to make sure if your WAL archiving is enabled. You can look at Postgres.conf under cluster data file. Following steps you can connect your database as super user to continue but I'll use psql in bash. Access your environment and connect as postgres user and continue as bellow; [root@gunce]#sudo su - postgres -bash-4.2$ psql -d backup_test -c "select pg_start_backup(' backuptest ');"  pg_start_backup -----------------  0/F000028 (1 row) -bash-4.2$ tar -czf gunce.tar.gz data/* -C /var/lib/pgsql/9.6/ -bash-4.2$ psql -d  backup_test  -c "select pg_stop_backup();" NOTICE:  pg_stop_backup complete, all required WAL segments have been archived  pg_stop_backup ----------------  0/F000130 (1 row)

Hi, This error mention that WAL Segments can not be archived, so you need to check archive_command is right to archive WAL.  NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived  WARNING: pg_stop_backup still waiting for all required WAL segments to be archived (60 seconds elapsed)  HINT: Check that your archive_command is executing properly. pg_stop_backup can be cancelled safely, but the database backup will not be usable without all the WAL segments. archive_command can be as bellow row in your postgres.conf file; archive_command = '/bin/cp %p /var/lib/pgsql/archive/%f' the most important thing is if there is a directory for /var/lib/pgsql/archive/%f side. Check it out and reload your config file or restart your Postgresql server. Loves,

Hi, The reason getting bellow error is relevant your postgres.conf file. ERROR:  WAL level not sufficient for making an online backup HINT:  wal_level must be set to "replica" or "logical" at server start. If you faced it, you should check status of was_level and archive_mode in Postgres.conf file, you can find it under postgresql's data file. Check both of two rows in your file; wal_level = replica archive_mode = on Loves,