Günce Kaya

Hi all, If you have lots of user and table/schema on your database you can not give permission per table but you can follow this steps. It's gonna be manual process but sometimes you can not give whole privileges on entire schema in your databases to users/roles. So, in that case you should generate your scripts. GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO your_role; GRANT USAGE ON SCHEMA schema_name TO your_role; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schema_name TO your_role; If you don't want to give all privileges and make specific for users also you can generate a sql script for whole schema for user or your all users like; Generate GRANT script for granting privileges whole schemas for a role; SELECT DISTINCT 'GRANT SELECT ON ALL TABLES IN SCHEMA '|| schemaname || 'TO user_role;' FROM pg_stat_user_tables; Generate USAGE script for whole schemas; SELECT DISTINCT 'GRANT USAGE ON SCHEMA '|| schemaname || 'TO user_role;

Merhaba, PostgreSQL 10 ile gelen yeniliklerden biri olan Logical Replication'dan bahsetmek isterim. Logical Replication (LR), veri objelerini ve üzerlerindeki değişiklikleri, replikasyon kimliklerine göre (primary key gibi) replike eder. Yani aslında publisher veritabanından datanın snapshot’ının kopyalanmasıdır. Tüm değişiklikler tamamlandığında publisher üzerindeki değişiklikler subscriber’a real time olarak yollanır. LR ile sadece normal tablolar replike edilebilir. Materialized view, view, partition’un root tablosu yada foreign tabloları replike edilemediği göz önünde tutulmalıdır. Ayrıca şema bazlı veri aktarımı yapılamamakta. Farklı PostgreSQL versiyonları arasında LR yapılabilir. Birden fazla veritabanı üzerinden tek bir veritabanına LR yapılabilir. Farklı kullanıcı gruplarına replike olmuş datalar için yetki verilebilir. Veri objesi üzerinde yapılan her tekil değişiklik karşı tarafa aktarılır. Tek bir tablonun replikasyonu birden farklı cluster’lar üzerinde olabilir. LR i

Hi, When PostgreSQL server and cluster versions don't match, PostgreSQL doesn't let you to run some functions, therefore you can not use efficiently and can not be advanced on it. To solve this problem you need to upgrade version or follow bellow steps. You can try all of steps for your development or test environments. I installed PostgreSQL server and client with different versions to show what is gonna happen If I try to dump. For sure, I can not get pg_dump! -bash-4.2$ pg_dump -s testdb > testdb_dump.sql pg_dump: server version: 9.6.3; pg_dump version: 9.2.18 pg_dump: aborting because of server version mismatch -bash-4.2$ pg_ctl stop -m fast waiting for server to shut down.... done server stopped -bash-4.2$ mv /var/lib/pgsql /tmp/pgsql_cluster_old mv: cannot move ‘/var/lib/pgsql’ to ‘/tmp/pgsql_cluster_old’: Permission denied To move pgsql file, need to be root at this moment. -bash-4.2$ logout [root@gunce-test tmp]# mv /var/lib/pgsql /tmp/pgsql_clu

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,

Hello, This error indicate that user's permission have some absences like PTPT1000 or HCCPCSSA1010 or HCDPALL.. add one of that permissions or give a role that has permission upper ones.  If you want you can add on permission list as bellow, Loves,

Hello, I have created a package and procedure that include a function which defined in package body. This function has three parameter in package. When I change number of parameter of function in package body, I've got PLS-00323 error. Instance of package; CREATE OR REPLACE package SYSPROD.test_procedure is     function test_inv (p_emplid in number,p_seqno in number,p_date in varchar2) return varchar2;     end test_procedure; / Instance of package body; CREATE OR REPLACE package body SYSPROD.test_procedure is     function test_inv (p_emplid in number,p_seqno in number,p_date in varchar2,p_invoice_address  varchar2)  return varchar2 is        PRAGMA AUTONOMOUS_TRANSACTION;     cursor c_cursor is                      /*sql statement as you need....                        ........................                        ........................*/                     /* Declarations ......*/       begin        .....     end; end test_procedure; You can se

Hello, If you create a package body and already close edit page, for editing package body, open schema browser in TOAD than right click and chose Load in Editor and do your changes in package body. Loves,

Hello, I've created a procedure and in the running time I've got that error. Make sure your scheme must have privileges to the tables that you use in your cursor in pl/sql script. I run bellow code and It works to me now. GRANT SELECT ON scheme_name.your_table_name TO your_scheme; Loves,

Hello, I got this error when I run insert script with select statement. This error about tablespaces. I just increased tablespace size. ALTER TABLESPACE PSTEMP ADD TEMPFILE '/data/PSPRD/oradata/pstemp_4.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 500M  MAXSIZE unlimited; Loves,

Hi, When I try to install Oracle client to windows machine, I got "INS-32025 Error" error. To overcome with that issue, go to C:\Program Files\Oracle\Inventory and delete Inventory file. Loves,

Hello, If you get this error when you run your trigger, you need to control your sql script. take out your into statement from your sql, run it and check it, If your result of sql script returns more than one row. Loves,

Hello, You can use bellow code for this subject; Local Grid &MYGRID; Local GridColumn &COLUMN; &MYGRID = GetGrid(Page."PAGE_NAME", "GRID_NAME"); If some_conditions.. Then    &MYGRID.GetColumn("FIELD_NAME").Visible = True;    &MYGRID.GetColumn("FIELD_NAME2").Visible = True; Else    &MYGRID.GetColumn("FIELD_NAME").Visible = False;    &MYGRID.GetColumn("FIELD_NAME2").Visible = False; End-If; Loves,

Hello, Sometimes,  when you use dynamic drop down list with peoplecode, having trouble with invalid value become unavoidable. When I click "Getir" button, Class Name field change invalid value even before using that button I could see true value for that field. I just only check my code that class name is changed. The value that comes in drop down list for class name doesn't pair to the value that comes from Getir button.  You can also control character length of field of drop down list. I checked values with winmessage function and I aware both of values doesn't match. That's all.  Loves,

Hello, With using collapsible Group boxes you can minimize all fields and free up space in your pages. If your page has lots of field and you can make it understandable. Before use it, you should decide to where you want to use Collapsible Group Boxes. In your page; Add a new group box which include your fields that you want to hide or show all of them. Double click head of group box and open Group Box Properties. Give a name for group box's label in Label tab.

Hello, There are lots of script that indicate how you can do that but there is a trick that you need to give a name for specified column. You can use following script; Local Grid &MYGRID; Local GridColumn &COLUMN; &MYGRID = GetGrid(Page."YOUR_PAGE_NAME", "GRID'S_NAME"); If &v_SSR_RES_MARK_OPT = "Y" Then    &MYGRID.GetColumn("COLUMN_NAME").Visible = True; Else    &MYGRID.GetColumn("COLUMN_NAME").Visible = False; End-If; Before using this script change grid's column name. To do that double click column and open page field properties. On General tab, change name of Page Field Name as you want than use this name in bellow row in your code and use it instead of COLUMN_NAME &COLUMN = &MYGRID.GetColumn("COLUMN_NAME"); PS: Do not use table/view's field name for column_name. Loves,