tag:blogger.com,1999:blog-33010741445128048892024-03-05T00:11:06.477-08:00Database TechnologiesVibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.comBlogger85125tag:blogger.com,1999:blog-3301074144512804889.post-82309346811601396262015-10-07T12:15:00.000-07:002015-10-07T12:15:11.716-07:00Tip:: PPAS 9.4 and Global Temporary TableCustomers who moved/migrated their database from Oracle to PPAS frequently ask for <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="75b75148-e84a-4a78-af32-11a1aaec17ce" id="40361ca2-ba26-4289-a16f-5950e81a34f6">Global Temporary Table</gs> in PPAS.<br />
<br />
Currently, PPAS doesn't support Global Temporary tables. However, there is a way user can achieve this functionality in PPAS.<br />
<br />
Before we continue with the implementation, lets first understand <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="1f49e9bd-5584-409a-9871-9c3677f4d83f" id="1f63c3ce-e90a-41ac-b258-2c340d4d353d">characteristics</gs> of Global Temporary Table. Following are the important characteristics of Global Temporary Table.<br />
1. Global Temporary Table gives predefined structure for storing data.<br />
2. It's <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="c10b0c1d-be98-4902-9646-cde8c3aea5fc" id="d25557ba-9be1-4ee7-8e79-8e7677d29513">an</gs> <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="c10b0c1d-be98-4902-9646-cde8c3aea5fc" id="05a752ca-2817-4826-94d0-a94e67fd407c">unlogged</gs> <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="c10b0c1d-be98-4902-9646-cde8c3aea5fc" id="a850eaa3-db76-4ff3-b4ad-3031ccb4b065">table which</gs> means any activity on this table will not be logged.<br />
3. The data in a global temporary table are private, such that data inserted by a session can only be accessed by that session.<br />
<br />
Based on the above characteristics of Global Temporary Table AKA GTT, we can define similar kind of work by using the following method:<br />
1. Create UNLOGGED TABLE in PPAS, which activity won't be logged.<br />
2. Create Row Level Security in such a way that session should be able to see their information (based on PID).<br />
3. Create a process which can cleanup data from GTT based on <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="3051faa6-96a4-4dd4-949d-3799a52da9ca" id="e7ccc8f0-c0e7-4e75-8e79-839463f01d64">pids</gs> which are not active in <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="3051faa6-96a4-4dd4-949d-3799a52da9ca" id="f4a51d74-78c6-4339-a0a6-ea3416060940">database</gs>.<br />
<br />
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d6273101-ae67-491f-b2f4-2c4b4054065e" id="e08530fb-df70-4fb5-81e1-1a2a147fb80a">Lets</gs> see how we can implement it <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d6273101-ae67-491f-b2f4-2c4b4054065e" id="97bd7771-4457-4baf-96fa-d6d8d1c1bc27">in</gs> <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d6273101-ae67-491f-b2f4-2c4b4054065e" id="625b8a21-b92b-45b9-bb8a-c1efa8a699b8">Adavanced</gs> Server.<br />
<br />
1. Create an UNLOGGED table with all columns required and extra column of <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="c1f9400b-1340-436a-ac8c-759b7e64e259" id="73baf44a-54d1-4b13-bd99-be339af0220e">Pid</gs>.<br />
<br />
<pre class="cpp" name="code">CREATE UNLOGGED TABLE test_global_temporary_table
</pre>
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="6efecc16-1308-45f5-98f6-8ee7a660accf" id="de7f5784-b258-42ee-a17e-8e0d7cb67e65">(</gs>id numeric, col text, <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="6efecc16-1308-45f5-98f6-8ee7a660accf" id="20a3d6d3-508a-4c9f-ae0b-1c08b8803077">pid</gs> <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="6efecc16-1308-45f5-98f6-8ee7a660accf" id="08f0a54a-fb20-433a-a8b4-4fd08a47a300">bigint</gs> default pg_backend_pid<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="6efecc16-1308-45f5-98f6-8ee7a660accf" id="2c7c98d6-1751-4ce7-94fc-f96377ac7ca0">(</gs>));<br />
<br />
2. Create a function to restrict the visibility of data.<br />
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION verify_pid_context (
p_schema TEXT,
p_object TEXT
)
RETURN VARCHAR2
IS
DECLARE
predicate TEXT;
BEGIN
IF ( current_setting('is_superuser') = 'on')
THEN
predicate = 'true';
ELSE
predicate := format('pid = %s',pg_backend_pid());
END IF;
RETURN predicate;
END;
</pre>
<br />
<br />
3. Apply the security policy based on above function.<br />
<br />
<pre class="cpp" name="code">DECLARE
v_object_schema VARCHAR2(30) := 'public';
v_object_name VARCHAR2(30) := 'test_global_temporary_table';
v_policy_name VARCHAR2(30) := 'secure_by_pid';
v_function_schema VARCHAR2(30) := 'public';
v_policy_function VARCHAR2(30) := 'verify_pid_context';
v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
v_update_check BOOLEAN := TRUE;
v_enable BOOLEAN := TRUE;
BEGIN
DBMS_RLS.ADD_POLICY( v_object_schema,
v_object_name,
v_policy_name,
v_function_schema,
v_policy_function,
v_statement_types,
v_update_check,
v_enable
);
END;
</pre>
<br />
4. Create UPDATABLE view which can hide <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="207e2e3e-6345-4246-9c3a-f91573155d14" id="b535563e-d75e-46fa-b60a-65a8ff611b2f">pid column</gs>. All sessions will be using this view as GTT.<br />
<pre class="cpp" name="code"> CREATE OR REPLACE VIEW test_global_temporary AS SELECT id, col FROM test_global_temporary_table;
</pre>
<br />
<br />
5. Create a <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="4a6d0695-7868-4084-8f94-b2355054ca43" id="0c2336ba-fc11-4813-819f-d4602ee38d4f">backend</gs> job, which can cleanup Table based on stale/old sessions.<br />
For job, <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="e41dc6c4-c724-4f91-946e-b044ed088dd9" id="175b1976-a149-4ca7-97e9-e7a73fa34ff8">user/developer</gs> can do <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="e41dc6c4-c724-4f91-946e-b044ed088dd9" id="6b85899b-b4bc-4329-a49e-4a1a2b20a094">following</gs>:<br />
a. <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="5863619d-3e3a-4747-a89f-dabf86cf06a2" id="64c3d5fb-d399-4e12-b7fe-c00c24ec0ca2">use</gs> <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="5863619d-3e3a-4747-a89f-dabf86cf06a2" id="1995d77b-27df-45d6-9026-5ca7ab15892f">superuser</gs> and execute DELETE command on <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="5863619d-3e3a-4747-a89f-dabf86cf06a2" id="d27206af-fa0c-4c60-a060-cd652e3e3eda">table</gs>:<br />
DELETE FROM test_global_temporary WHERE <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d231c6c8-86ec-4684-95d0-05dc455371e0" id="b233067c-8a25-444a-b9cb-e0a19955625a">pid</gs> NOT in (SELECT <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d231c6c8-86ec-4684-95d0-05dc455371e0" id="b235e752-c174-47ad-8cf6-18365e7fe3da">pid</gs> FROM pg_stat_activity);<br />
b. To Schedule above DELETE command, <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="b15f3613-3014-4f26-a76b-31d67bf9be33" id="c854810c-1254-429b-ac3b-f4448482864b">user</gs> can use one of the following:<br />
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="ebacf267-0cd5-45e3-8eaf-0775e67532c4" id="f36a5c3e-2d4c-44d2-af9d-ae6e256e652b">i</gs>. <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="ce449604-33ae-4e50-a20e-4e7777d89f0d" id="85bc426e-4a19-426b-9fa8-f37a672ac715">Crontab</gs><br />
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="9c13c391-6eb3-44ac-842c-388b4917866c" id="017e1a07-7418-4af5-9232-2b588f04333e">ii</gs>. Or PPAS DBMS_SCHEDULE Package.<br />
<br />
6. GRANT ALL privileges to database user who can access Global Temporary Table.<br />
<br />
<pre class="cpp" name="code"> GRANT ALL on test_global_temporary TO testdbuser;
GRANT ALL on test_global_temporary_table To testdbuser;
</pre>
<br />
Now, lets try above implementation of Global Temporary Table.<br />
<br />
Open two sessions as a normal user (<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d3fb5e38-ffa3-44b3-b11f-52c903707d50" id="9be1685f-5812-48bf-8ea7-a339112e6138">testdbuser</gs>) as given below:<br />
<br />
<pre class="cpp" name="code">[vibhorkumar@localhost ~]$ psql -U testdbuser edb
psql.bin (9.4.4.9)
Type "help" for help.
edb=>
edb=> select pg_backend_pid();
pg_backend_pid
----------------
32722
(1 row)
edb=> select pg_backend_pid();
pg_backend_pid
----------------
32729
(1 row)
</pre>
<br />
<br />
Now from both <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="dbb6984d-f8b8-40fa-8a63-a791d4ad3a9a" id="9c3ba02e-55a0-458c-98d0-b81763dc49fa">session insert</gs> some records:<br />
From first session:<br />
<br />
<br />
<pre class="cpp" name="code">edb=> INSERT INTO test_global_temporary VALUES(1,'FROM pid 32722');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(2,'FROM pid 32722');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(3,'FROM pid 32722');
INSERT 0 1
</pre>
<br />
From Second session:<br />
<br />
<pre class="cpp" name="code">edb=> INSERT INTO test_global_temporary VALUES(1,'FROM pid 32729');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(2,'FROM pid 32729');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(3,'FROM pid 32729');
INSERT 0 1
</pre>
<br />
From First Session:<br />
<pre class="cpp" name="code">edb=> SELECT * FROM test_global_temporary;
id | col
----+----------------
1 | FROM pid 32722
2 | FROM pid 32722
3 | FROM pid 32722
(3 rows)
</pre>
<br />
<br />
From Second Session:<br />
<pre class="cpp" name="code">edb=> SELECT * FROm test_global_temporary;
id | col
----+----------------
1 | FROM pid 32729
2 | FROM pid 32729
3 | FROM pid 32729
(3 rows)
</pre>
<br />
<br />
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="0e1359a1-5e85-41fd-9210-ff9c0b99474e" id="35c5ca41-f306-4263-a356-8455b85d06ee">which</gs> shows that <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="0e1359a1-5e85-41fd-9210-ff9c0b99474e" id="1e202e80-ed14-4e94-85e4-725ff9e985f8">unlogged</gs> table with right RLS policy and <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="0e1359a1-5e85-41fd-9210-ff9c0b99474e" id="332da29a-2cc0-41ff-9f94-015c18f7c538">backend</gs> job, can be a potential solution for Global Temporary Tables.<br />
<br />Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-50062424923452578832015-08-06T08:53:00.000-07:002015-08-06T08:53:26.271-07:00Postgres And Transparent Data Encryption (TDE)<div class="MsoNormal">
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">Security has
always been a great concern of Enterprises. Especially, if you have crucial
information stored in the database, you would always prefer to have high
security around it. Over the years, technologies have evolved and provided
better solutions around it.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">If you have very
sensitive information, people try to keep this information encrypted so, that
in case, somebody gets access of the system, then they cannot view this
information, if they are not authorized.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">For managing
sensitive information, Enterprises use multiple methods:<o:p></o:p></span></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman";">1. Encrypting specific information. <o:p></o:p></span></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">If you are PPAS
users, you would like to use DBMS_CRYPTO package which provides a way of
encrypting sensitive information in databases.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">For more information,
please refer following link:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";"><a href="http://www.enterprisedb.com/docs/en/9.4/oracompat/Database_Compatibility_for_Oracle_Developers_Guide.1.178.html#">http://www.enterprisedb.com/docs/en/9.4/oracompat/Database_Compatibility_for_Oracle_Developers_Guide.1.178.html#</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">For PostgreSQL,
users can use pgcrypto module.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman";">2. Transparent Data Encryption (TDE) </span></b><span style="font-family: "Times New Roman";">is another method employed by both
Microsoft and Oracle to encrypt database files. TDE offers encryption at file
level. This method solves the problem of protecting data at rest i.e.
encrypting databases both on the hard drive and consequently on backup media.
Enterprises typically employ TDE to solve compliance issues such as PCI DSS.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">Postgres Plus,
currently doesn't have inbuilt TDE, however, if Enterprises looking for
encryption at the database file level, they can use one of the following
methods for protecting data at rest:<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman";">1.<span style="mso-spacerun: yes;">
</span>Full Disk Encryption:<o:p></o:p></span></b></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">Full disk or
partition encryption is one of the best ways of protecting your data. This
method not only protects each file, however, also protects the temporary
storage that may contain parts of these files.<span style="mso-spacerun: yes;">
</span>Full disk encryption protects all of your files and then you do not have
to worry about selecting what you want to protect and possibly missing a file.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">RHEL (Red Hat)
supports Linux Unified Key Setup-on-disk-format (or LUKS). LUKS bulk encrypts
Hard Drive partition.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">For more
information on LUKS, please refer following link:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security_Guide/chap-Security_Guide-Encryption.html#sect-Security_Guide-LUKS_Disk_Encryption<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman";">2. File system-level encryption:<o:p></o:p></span></b></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>File system-level encryption often called
file/directory encryption. In this method individual files or directories are
encrypted by the file system itself.<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">There is
stackable cryptographic file system encryption available which user can utilize
in their environment.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman";">File system level Encryption gives
following advantages</span></b><span style="font-family: "Times New Roman";">:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">1. Flexible
file-based key management, so that each file can be and usually is encrypted
with a separate encryption key.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">2. Individual
management of encrypted files e.g. Incremental backups of the individual
changed files even in encrypted form, rather than backup of the entire
encrypted volume.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">3. Access
control can be enforced through the use of public-key cryptography, and the
fact that cryptographic keys are only held in memory while the file that is
decrypted by them is held open. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">Stackable
cryptographic file system encryption can be use for Postgres for Transparent
Data Encryption. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">In this blog, I
will discuss using mount ecrpytfs as it requires less overhead in setup (LUKS
requires a new disk to be configured and formatted before storing data on it.
"mount ecrpytfs" works with existing directories and data).<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">If Enterprises
want to give the control to DBAs for TDE, they can use/define few sudo rules
for DBAs to execute commands for encryption.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman";">Following is a method, which they can
use:<o:p></o:p></span></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l1 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">1)<span style="font: 7.0pt "Times New Roman";">
</span></span></span><!--[endif]--><span style="font-family: "Times New Roman";">Ask
system admin to create sudo rules to allow DBA to execute encryption for data
directory for Postgres Plus. One common way to do this is using the “mount
ecryptfs” command in Linux operating systems. <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l1 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">2)<span style="font: 7.0pt "Times New Roman";"> </span></span></span><!--[endif]--><span style="font-family: "Times New Roman";">If
user needs to encrypt the /ppas94/data directory, they can use following
command: <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast">
<br /></div>
<div class="MsoNormal" style="margin-left: .5in; text-indent: .5in;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">sudo mount -t ecryptfs /ppas94/data
/ppas94/data</span><span style="font-family: Times New Roman;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span>More information can be found in the
documentation from RHEL:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";"><span style="mso-spacerun: yes;"> </span></span><a href="https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Storage_Administration_Guide/ch-efs.html"><span style="font-family: "Times New Roman";">https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Storage_Administration_Guide/ch-efs.html</span></a><span style="font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">User can also
specify encryption key type (passphrase, openssl), cipher (aes, des3_ede...)
key byte size, and other options with above commands. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman";">Example is given below:<o:p></o:p></span></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"># mount -t
ecryptfs /home /home -o ecryptfs_unlink_sigs \<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><span style="mso-spacerun: yes;"> </span>ecryptfs_key_bytes=16 ecryptfs_cipher=aes ecryptfs_sig=c7fed37c0a341e19<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">Centos 7 and
RHEL 7, by default doesn’t come with ecrpytfs therefore, user can also use
encfs command. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">For more
information on encfs, please refer following link:<o:p></o:p></span></div>
<div class="MsoNormal">
<a href="https://wiki.archlinux.org/index.php/EncFS"><span style="font-family: "Times New Roman";">https://wiki.archlinux.org/index.php/EncFS</span></a><span style="font-family: "Times New Roman";"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: "Times New Roman";">Following are the steps to use encfs to encrypt
the data directory.<o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";">
</span></span></span><!--[endif]--><span style="font-family: "Times New Roman";">Create
a data directory using following command, as enterprisedb user.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast">
<br /></div>
<div class="MsoNormal" style="margin-left: .25in;">
<span style="font-family: Courier New, Courier, monospace;"> <span style="font-size: x-small;"> mkdir /var/lib/ppas/9.4/encrypted_data<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-left: .25in;">
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> chmod 700 /var/lib/ppas/9.4/encrypted_data</span><span style="font-family: Times New Roman;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .25in;">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><!--[endif]--><span style="font-family: "Times New Roman";">Use
following encfs command to encrypt the data directory.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast">
<span style="font-family: 'Times New Roman';"><br /></span></div>
<div class="MsoListParagraphCxSpLast">
<span style="font-size: x-small;"><span style="font-family: 'Times New Roman';"> </span><span style="font-family: Courier New, Courier, monospace;"> encfs /var/lib/ppas-9.4/encrypted_data/
/var/lib/ppas-9.4/data</span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">Snapshot of above
command is given below:<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">encfs /var/lib/ppas/9.4/encrypted_data
/var/lib/ppas/9.4/data<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">The directory
"/var/lib/ppas/9.4/data" does not exist. Should it be created? (y,n)
y<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Creating new encrypted volume.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Please choose from one of the
following options:<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><span style="mso-spacerun: yes;"> </span>enter "x" for expert configuration
mode,<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><span style="mso-spacerun: yes;"> </span>enter "p" for pre-configured
paranoia mode,<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><span style="mso-spacerun: yes;"> </span>anything else, or an empty line will select
standard mode.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">?> p<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Paranoia configuration selected.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Configuration finished.<span style="mso-spacerun: yes;"> </span>The filesystem to be created has<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">the following properties:<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Filesystem cipher:
"ssl/aes", version 3:0:2<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Filename encoding: "nameio/block",
version 3:0:1<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Key Size: 256 bits<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Block Size: 1024 bytes, including 8
byte MAC header<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Each file contains 8 byte header with
unique IV data.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Filenames encoded using IV chaining
mode.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">File data IV is chained to filename
IV.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">File holes passed through to
ciphertext.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-------------------------- WARNING
--------------------------<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">The external initialization-vector
chaining option has been<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">enabled.<span style="mso-spacerun: yes;"> </span>This option disables the use of hard links on
the<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">filesystem. Without hard links, some
programs may not work.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">The programs 'mutt' and 'procmail' are
known to fail.<span style="mso-spacerun: yes;"> </span>For<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">more information, please see the encfs
mailing list.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">If you would like to choose another
configuration setting,<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">please press CTRL-C now to abort and
start over.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Now you will need to enter a password
for your filesystem.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">You will need to remember this
password, as there is absolutely<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">no recovery mechanism.<span style="mso-spacerun: yes;"> </span>However, the password can be changed<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">later using encfsctl.<o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">New Encfs Password: <o:p></o:p></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<span style="color: #3b2322;"><span style="font-size: x-small;"><span style="font-family: Courier New, Courier, monospace;">Verify Encfs Password: </span><span style="font-family: Courier New;"><o:p></o:p></span></span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-layout-grid-align: none; mso-pagination: none; tab-stops: 28.0pt 56.0pt 84.0pt 112.0pt 140.0pt 168.0pt 196.0pt 224.0pt 3.5in 280.0pt 308.0pt 336.0pt; text-autospace: none;">
<br /></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman";"><span style="mso-list: Ignore;">3.<span style="font: 7.0pt "Times New Roman";">
</span></span></span><!--[endif]--><span style="font-family: "Times New Roman";">After
encrypting, data directory, users also need to modify the
postgresql-<version> service script to include proper command in it for
password. For that either, they can use sshpass or they can write their own
program which can pass the password for mounting directory.<o:p></o:p></version></span></div>
<div class="MsoNormal">
<br /></div>
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
<o:PixelsPerInch>96</o:PixelsPerInch>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>JA</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
<w:UseFELayout/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="false"
DefSemiHidden="false" DefQFormat="false" DefPriority="99"
LatentStyleCount="380">
<w:LsdException Locked="false" Priority="0" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 6"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 7"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 8"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 9"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 9"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Normal Indent"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="footnote text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="annotation text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="header"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="footer"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index heading"/>
<w:LsdException Locked="false" Priority="35" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="table of figures"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="envelope address"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="envelope return"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="footnote reference"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="annotation reference"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="line number"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="page number"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="endnote reference"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="endnote text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="table of authorities"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="macro"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="toa heading"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number 5"/>
<w:LsdException Locked="false" Priority="10" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Closing"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Signature"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="true"
UnhideWhenUsed="true" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text Indent"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Message Header"/>
<w:LsdException Locked="false" Priority="11" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Salutation"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Date"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text First Indent"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text First Indent 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Heading"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text Indent 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text Indent 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Block Text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Hyperlink"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="FollowedHyperlink"/>
<w:LsdException Locked="false" Priority="22" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Document Map"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Plain Text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="E-mail Signature"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Top of Form"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Bottom of Form"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Normal (Web)"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Acronym"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Address"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Cite"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Code"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Definition"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Keyboard"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Preformatted"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Sample"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Typewriter"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Variable"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Normal Table"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="annotation subject"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="No List"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Outline List 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Outline List 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Outline List 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Simple 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Simple 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Simple 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Classic 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Classic 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Classic 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Classic 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Colorful 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Colorful 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Colorful 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 6"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 7"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 8"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 6"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 7"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 8"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table 3D effects 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table 3D effects 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table 3D effects 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Contemporary"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Elegant"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Professional"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Subtle 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Subtle 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Web 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Web 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Web 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Balloon Text"/>
<w:LsdException Locked="false" Priority="39" Name="Table Grid"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Theme"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 6"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 7"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 8"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Level 9"/>
<w:LsdException Locked="false" SemiHidden="true" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" SemiHidden="true" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" QFormat="true"
Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" QFormat="true"
Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" QFormat="true"
Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" QFormat="true"
Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" QFormat="true"
Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" QFormat="true"
Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" SemiHidden="true"
UnhideWhenUsed="true" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="TOC Heading"/>
<w:LsdException Locked="false" Priority="41" Name="Plain Table 1"/>
<w:LsdException Locked="false" Priority="42" Name="Plain Table 2"/>
<w:LsdException Locked="false" Priority="43" Name="Plain Table 3"/>
<w:LsdException Locked="false" Priority="44" Name="Plain Table 4"/>
<w:LsdException Locked="false" Priority="45" Name="Plain Table 5"/>
<w:LsdException Locked="false" Priority="40" Name="Grid Table Light"/>
<w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark"/>
<w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful"/>
<w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 1"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 1"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 1"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 1"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 1"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 2"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 2"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 2"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 2"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 2"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 3"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 3"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 3"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 3"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 3"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 4"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 4"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 4"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 4"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 4"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 5"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 5"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 5"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 5"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 5"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 6"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 6"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 6"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 6"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 6"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 1"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 1"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 1"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 1"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 1"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 2"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 2"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 2"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 2"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 2"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 3"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 3"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 3"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 3"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 3"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 4"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 4"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 4"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 4"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 4"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 5"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 5"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 5"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 5"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 5"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 6"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 6"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 6"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 6"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 6"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 6"/>
</w:LatentStyles>
</xml><![endif]-->
<!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:Calibri;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}
</style>
<![endif]-->
<!--StartFragment-->
<!--EndFragment--><br />
<div class="MsoNormal">
<span style="font-family: "Times New Roman";">As you can see,
achieving Transparent Data Encryption Postgres is very easy. <o:p></o:p></span></div>
</div>
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-41702722957355098402014-12-10T14:47:00.000-08:002014-12-11T10:51:49.570-08:00Dynamic RLS implementation in PPAS 9.3In the course of my work at EnterpriseDB, migrating Oracle databases to EnterpriseDB's Postgres Plus Advanced Server is a common task. However, now and then we encounter unique situations. While working on a migration project recently, we encountered a new use case for RLS (Row level Security).<br />
<br />
The customer had a centralized database where it stored a huge number of transactions. These transactions are performed by different business units located in different parts of the world. There are certain types of transactions that should not be visible even if they are being queried by the same company. That is where RLS comes in. With RSL, specific transactions, or kinds of transactions, that can remain visible are mapped back to an attribute in the table.<br />
<br />
The customer needed the application to authenticate users and set the context for which records in the database become visible for a specific session.
In its deployment of Oracle, the customer had used the functions/procedure in the Oracle package DBMS_SESSION. In the application, the customer used DBMS_SESSION.SET_CONTEXT to set the context. And for the Row Level Security, the customer was using the DBMS_SESSION.SYS_CONTEXT to implement security around the transactions.
<br />
<br />
Postgres Plus Advanced Server has a DBMS_SESSION package that is compatible with Oracle. However, it does not currently offer users the capability of setting the user defined context and implementing RLS based on those context. Given others may experience similar situations , as our customer, I wanted to provide the procedures and functions that users could deploy.<br />
<br />
SET_CONTEXT procedure.<br />
<br />
The definition of this procedure is given below:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE PROCEDURE set_context(namespace TEXT,
attribute TEXT,
val TEXT)
AS
BEGIN
EXECUTE IMMEDIATE format('SET %s.%s TO %s',namespace, attribute,val);
END;
</pre>
Using this procedure, users can set their own context at session level.<br />
<br />
The following is a function to help view the context in session, which is set using the above procedure.
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION USYS_CONTEXT(namespace TEXT,
parameter TEXT,
len BIGINT DEFAULT 8)
RETURN TEXT
AS
DECLARE
return_val TEXT;
BEGIN
EXECUTE IMMEDIATE format('SHOW %s.%s',namespace,parameter) INTO return_val;
RETURN substr(return_val,1,len);
EXCEPTION WHEN others THEN
RETURN NULL;
END;
</pre>
The following is an example of how we can implement row level security based on the above procedure and functions:<br />
<br />
1. Create a table which will have attribute context_check to map the context set by procedure:
<br />
<pre class="cpp" name="code">CREATE TABLE test_rls(id numeric, col text, context_check text);
INSERT INTO test_rls SELECT id, 'First_check','aaa' FROM generate_series(1,10) foo(id);
INSERT INTO test_rls SELECT id, 'First_check','bbb' FROM generate_series(1,10) foo(id);
INSERT INTO test_rls SELECT id, 'First_check','ddd' FROM generate_series(1,10) foo(id);
</pre>
2. Now create a function to check the application context. Below is one function:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION verify_user_context (
p_schema TEXT,
p_object TEXT
)
RETURN VARCHAR2
IS
DECLARE
predicate TEXT;
BEGIN
predicate := format('context_check = public.usys_context(''%s''::text,''%s''::text, 8)','CONTEXT','APP_PREDICATE');
RETURN predicate;
END;
</pre>
3. Now Apply Security Policy using Policy Functions shown below:
<br />
<pre class="cpp" name="code">DECLARE
v_object_schema VARCHAR2(30) := 'public';
v_object_name VARCHAR2(30) := 'test_rls';
v_policy_name VARCHAR2(30) := 'secure_data';
v_function_schema VARCHAR2(30) := 'public';
v_policy_function VARCHAR2(30) := 'verify_user_context';
v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
v_update_check BOOLEAN := TRUE;
v_enable BOOLEAN := TRUE;
BEGIN
DBMS_RLS.ADD_POLICY(
v_object_schema,
v_object_name,
v_policy_name,
v_function_schema,
v_policy_function,
v_statement_types,
v_update_check,
v_enable
);
END;
</pre>
Now we are set to test this implementation.
Connect to one session and try the following:<br />
<br />
1. Set the context using procedure SET_CONTEXT as given below:
<br />
<pre class="cpp" name="code">EXEC SET_CONTEXT('CONTEXT','APP_PREDICATE','ddd');
EDB-SPL Procedure successfully completed
</pre>
2. Verify in the same session to determine if we have set the Context properly:
<br />
<pre class="cpp" name="code"> SELECT USYS_CONTEXT('CONTEXT','APP_PREDICATE',2000);
usys_context
--------------
ddd
(1 row)
</pre>
3. Since in session, we have Context set as ddd, there in this session, we should be able to see rows respective to set contexts:
<br />
<pre class="cpp" name="code">beta=# SELECT * FROM test_rls ;
id | col | context_check
----+-------------+---------------
1 | First_check | ddd
2 | First_check | ddd
3 | First_check | ddd
4 | First_check | ddd
5 | First_check | ddd
6 | First_check | ddd
7 | First_check | ddd
8 | First_check | ddd
9 | First_check | ddd
10 | First_check | ddd
(10 rows)
</pre>
As you can see, the DBMS_RLS package in Postgres Plus Advanced Service can help in implementing Row Level Security based on Application Context.
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-76024739472954043212014-12-01T12:21:00.000-08:002014-12-01T12:28:41.767-08:00Compiling PLV8 with Postgres Plus Advanced ServerPLV8 is a programming language that lets users write stored procedures and triggers in JavaScript and store them in their Postgres database. This allows application programmers to write a lot of their server-side programming in the same language they use to build their web client applications. Fewer languages to learn usually means fewer mistakes and faster time to completion. The extensive language support is one of many reasons why Postgres’ use across the world is increasing lately. The recent addition of document data support with JSON and JSONB data types in PostgreSQL, and in Postgres Plus Advanced Server from EnterpriseDB, is the main reason for the increasing interest in the PL/V8 language extension. <br />
<br />
Below are the steps you need to compile PLV8 with Postgres Plus Advanced Server 9.3/9.4.<br />
<br />
To get started, here are the prerequisites:<br />
1. A supported version of PostgreSQL or Postgres Plus Advanced Server, such as versions 9.1 and higher.<br />
2. V8 version 3.14.5<br />
3. g++ version 4.5.1<br />
<br />
If you want to know more about V8, you can visit the following wiki page:<br />
http://en.wikipedia.org/wiki/V8_(JavaScript_engine)<br />
<br />
It’s important to note that when compiling PLV8 with Postgres Plus Advanced Server 9.3 or the upcoming 9.4, you will get the following two types of error messages:<br />
<br />
The first error:<br />
<pre class="cpp" name="code">[root@localhost plv8js]# make
sed -e 's/^#undef PLV8_VERSION/#define PLV8_VERSION "1.5.0-dev1"/' plv8_config.h.in > plv8_config.h
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o plv8.o plv8.cc
plv8.cc: In function ‘void _PG_init()’:
plv8.cc:226: error: invalid conversion from ‘void (*)(XactEvent, void*)’ to ‘void (*)(XactEvent, void*, bool)’
plv8.cc:226: error: initializing argument 1 of ‘void RegisterXactCallback(void (*)(XactEvent, void*, bool), void*)’
make: *** [plv8.o] Error 1
</pre>
<br />
The above error message is a result of a different signature of typedef void (*XactCallback) in the Advanced Server transaction system.<br />
<br />
To fix the above issue, the user can replace the following in plv8.cc:<br />
<pre class="cpp" name="code">static void plv8_xact_cb(XactEvent event, void *arg);
</pre>
With<br />
<pre class="cpp" name="code">static void plv8_xact_cb(XactEvent event, void *arg, bool spl_context);
</pre>
<br />
The second error:<br />
After making the above changes, you may get the following error after trying to compile the source code using the “make” command: <br />
<pre class="cpp" name="code">[root@localhost plv8js]# make
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o plv8.o plv8.cc
plv8.cc:137: warning: ‘void plv8_xact_cb(XactEvent, void*, bool)’ used but never defined
plv8.cc:232: warning: ‘void plv8_xact_cb(XactEvent, void*)’ defined but not used
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o plv8_type.o plv8_type.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o plv8_func.o plv8_func.cc
In file included from plv8_param.h:11,
from plv8_func.cc:9:
/usr/ppas-9.4/include/server/nodes/params.h:77: error: expected ‘,’ or ‘...’ before ‘typeid’
make: *** [plv8_func.o] Error 1
</pre>
<br />
The above is mainly due to the use of typeid in params.h; typeid is the reserved keyword of C++ compiler.<br />
<br />
To fix this issue, make the following changes in plv8.h<br />
<pre class="cpp" name="code">extern "C" {
#include "postgres.h"
#include "access/htup.h"
#include "fmgr.h"
#include "mb/pg_wchar.h"
#include "utils/tuplestore.h"
#include "windowapi.h"
}
</pre>
<br />
with<br />
<pre class="cpp" name="code">#define typeid __typeid
extern "C" {
#include "postgres.h"
#include "access/htup.h"
#include "fmgr.h"
#include "mb/pg_wchar.h"
#include "utils/tuplestore.h"
#include "windowapi.h"
}
#undef typeid
</pre>
<br />
In plv8_param.h, change the following:<br />
<pre class="cpp" name="code">extern "C" {
#include "postgres.h"
/*
* Variable SPI parameter is since 9.0. Avoid include files in prior versions,
* as they contain C++ keywords.
*/
#include "nodes/params.h"
#if PG_VERSION_NUM >= 90000
#include "parser/parse_node.h"
#endif<span class="Apple-tab-span" style="white-space: pre;"> </span>// PG_VERSION_NUM >= 90000
} // extern "C"
</pre>
<br />
With<br />
<pre class="cpp" name="code">#define typeid __typeid
extern "C" {
#include "postgres.h"
/*
* Variable SPI parameter is since 9.0. Avoid including files in prior versions,
* as they contain C++ keywords.
*/
#include "nodes/params.h"
#if PG_VERSION_NUM >= 90000
#include "parser/parse_node.h"
#endif<span class="Apple-tab-span" style="white-space: pre;"> </span>// PG_VERSION_NUM >= 90000
} // extern "C"
#undef typeid
</pre>
<br />
In plv8_param.cc, replace following:<br />
<pre class="cpp" name="code">extern "C" {
#include "catalog/pg_type.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
} // extern "C"
</pre>
<br />
with<br />
<pre class="cpp" name="code">#define typeid __typeid
extern "C" {
#include "catalog/pg_type.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
} // extern "C"
#undef typeid
</pre>
<br />
After making the above changes, you will be able to compile PLV8 with Advanced Server as shown below:<br />
<pre class="cpp" name="code">[root@localhost plv8js]# make
sed -e 's/^#undef PLV8_VERSION/#define PLV8_VERSION "1.5.0-dev1"/' plv8_config.h.in > plv8_config.h
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o plv8.o plv8.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o plv8_type.o plv8_type.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o plv8_func.o plv8_func.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o plv8_param.o plv8_param.cc
echo "extern const unsigned char coffee_script_binary_data[] = {" >coffee-script.cc
(od -txC -v coffee-script.js | \
<span class="Apple-tab-span" style="white-space: pre;"> </span>sed -e "s/^[0-9]*//" -e s"/ \([0-9a-f][0-9a-f]\)/0x\1,/g" -e"\$d" ) >>coffee-script.cc
echo "0x00};" >>coffee-script.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o coffee-script.o coffee-script.cc
echo "extern const unsigned char livescript_binary_data[] = {" >livescript.cc
(od -txC -v livescript.js | \
<span class="Apple-tab-span" style="white-space: pre;"> </span>sed -e "s/^[0-9]*//" -e s"/ \([0-9a-f][0-9a-f]\)/0x\1,/g" -e"\$d" ) >>livescript.cc
echo "0x00};" >>livescript.cc
g++ -Wall -O2 -DV8_USE_UNSAFE_HANDLES -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -fPIC -c -o livescript.o livescript.cc
g++ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o plv8.so plv8.o plv8_type.o plv8_func.o plv8_param.o coffee-script.o livescript.o -L/usr/ppas-9.4/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/ppas-9.4/lib',--enable-new-dtags -lv8
sed -e 's/@PLV8_VERSION@/1.5.0-dev1/g' plv8.control.common | g++ -E -P -DLANG_plv8 - > plv8.control
sed -e 's/@LANG_NAME@/plv8/g' plv8.sql.common | g++ -E -P -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -DLANG_plv8 - > plv8--1.5.0-dev1.sql
sed -e 's/@PLV8_VERSION@/1.5.0-dev1/g' plv8.control.common | g++ -E -P -DLANG_plcoffee - > plcoffee.control
sed -e 's/@LANG_NAME@/plcoffee/g' plv8.sql.common | g++ -E -P -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -DLANG_plcoffee - > plcoffee--1.5.0-dev1.sql
sed -e 's/@PLV8_VERSION@/1.5.0-dev1/g' plv8.control.common | g++ -E -P -DLANG_plls - > plls.control
sed -e 's/@LANG_NAME@/plls/g' plv8.sql.common | g++ -E -P -I. -I./ -I/usr/ppas-9.4/include/server -I/usr/ppas-9.4/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -DLANG_plls - > plls--1.5.0-dev1.sql
</pre>
<br />
<pre class="cpp" name="code">/bin/mkdir -p '/usr/ppas-9.4/lib'
/bin/mkdir -p '/usr/ppas-9.4/share/extension'
/bin/mkdir -p '/usr/ppas-9.4/share/extension'
/usr/bin/install -c -m 755 plv8.so '/usr/ppas-9.4/lib/plv8.so'
/usr/bin/install -c -m 644 plv8.control '/usr/ppas-9.4/share/extension/'
/usr/bin/install -c -m 644 plv8.control plv8--1.5.0-dev1.sql plcoffee.control plcoffee--1.5.0-dev1.sql plls.control plls--1.5.0-dev1.sql '/usr/ppas-9.4/share/extension/'
</pre>
<br />
After compiling PLV8, you now can install the PLV8 language in Advanced Server using the following command:<br />
<br />
<pre class="cpp" name="code">beta=# CREATE EXTENSION PLV8;
CREATE EXTENSION
beta=#
</pre>
<br />
To test your installed PLV8, here is some sample code:<br />
<pre class="cpp" name="code">beta=# DO $$ PLV8.elog(NOTICE, 'this', 'is', 'inline', 'code') $$ LANGUAGE PLV8;
NOTICE: this is inline code
DO
beta=# CREATE TYPE rec AS (i integer, t text);
CREATE TYPE
beta=# CREATE FUNCTION set_of_records() RETURNS SETOF rec AS
beta-# $$
beta$# // PLV8.return_next() stores records in an internal tuplestore,
beta$# // and return all of them at the end of function.
beta$# PLV8.return_next( { "i": 1, "t": "a" } );
beta$# PLV8.return_next( { "i": 2, "t": "b" } );
beta$#
beta$# // You can also return records with an array of JSON.
beta$# return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ];
beta$# $$
beta-# LANGUAGE PLV8;
CREATE FUNCTION
beta=# SELECT * FROM set_of_records();
i | t
---+---
1 | a
2 | b
3 | c
4 | d
(4 rows)
</pre>
<br />
In case you need a patched version of PLV8, use the following git repository:<br />
<pre class="cpp" name="code"> https://github.com/vibhorkum/PLV8_FOR_PPAS.git PLV8_ppas
</pre>
<br />
To use this, execute the following command:<br />
<pre class="cpp" name="code"> git clone https://github.com/vibhorkum/PLV8_FOR_PPAS.git PLV8_ppas
cd PLV8_ppas
make
make install
</pre>
<br />
To test the compiled PLV8, you can use the following command:<br />
<pre class="cpp" name="code">[root@localhost plv8js]# make installcheck
/usr/ppas-9.4/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --psqldir='/usr/ppas-9.4/bin' --dbname=contrib_regression init-extension plv8 inline json startup_pre startup varparam json_conv window dialect
(using postmaster on Unix socket, port 5444)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test init-extension ... ok
test plv8 ... ok
test inline ... ok
test json ... ok
test startup_pre ... ok
test startup ... ok
test varparam ... ok
test json_conv ... ok
test window ... ok
test dialect ... ok
======================
All 10 tests passed.
======================
</pre>
<div>
<br /></div>
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-41407756563877696812014-11-18T09:32:00.000-08:002014-12-01T16:22:03.457-08:00Meet BART – A New Tool for Backup And Recovery Management <div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">EnterpriseDB recently launched a new tool for backup and recovery – named simply EDB Backup and Recovery Tool, or <strong style="font-style: inherit; line-height: 1.7;">BART</strong>. This tool makes the DBA’s life easier by simplifying the tasks for managing their Postgres physical backup and recovery tasks, whether they are PostgreSQL or Postgres Plus Advanced Server deployments.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">BART has the following advantages over custom scripts for managing backups:</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">1.</strong> It’s stable and it uses the tool pg_basebackup to take a physical backup. This tool has been well defined and is well-supported by the PostgreSQL community.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">2.</strong> It catalogs all of the backups users are taking, which is important in terms of:</span><br />
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;"> i.</strong> Listing the type of backups used</span><br />
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;"> ii.</strong> Listing the status of those backups with server information.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">3.</strong> BART also provides functionality to restore backups, with all required archived WAL files. So automation around this tool will make DBAs’ lives easier for restore and recovery.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">4.</strong> BART provides an option to validate your backup by using checksum. This is useful for confirming you took a valid backup and it is not corrupted at disk level.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">5.</strong> BART provides an option to define your retention policy around the backups you are keeping.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">Given all of the above advantages, I decided to give this new tool a try and share some tips. To get started, you need the following <strong style="font-style: inherit; line-height: 1.7;">prerequisites</strong>:</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">1</strong>. BART currently requires a Linux 64 bit platform, CentOS 6.x or RHEL 6.x</span><br />
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">2</strong>. Need to have password-less, direct SSH access to the target machine where you want to restore backups as well as the database servers you want backed up</span><br />
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">3.</strong> Install the Postgres Plus Advanced Server or PostgreSQL binaries for pg_basebackup</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">Yum or rpm</span></strong></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">To install this tool, you have two options that I will explore below:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">1. Yum command
2. Rpm command.</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">Using the yum command:</span></strong></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">To perform a yum command installation, BART users can ask EDB for credentials to the EnterpriseDB yum repository and configure the their local yum repository as follows:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">echo "[tools]
name=EnterpriseDB Tools
baseurl=http://username:password@yum.enterprisedb.com/tools/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=0" >/etc/yum.repos.d/edbtools.repo</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">After creating the yum repo, the user can execute the following command to install BART:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">yum install edb-bart</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">If the user doesn't want to install the EDB Backup and Recovery Tool using the yum command, then the user can download a free standing rpm using the link below from EDB’s website:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">http://www.enterprisedb.com/downloads/postgres-postgresql-downloads</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">and then enter the rpm install command as follows:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">rpm -ivh edb-bart-1.0.1-1.rhel6.x86_64.rpm</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">After installing BART using the above commands, the user can see the binaries in the directory:/usr/edb-bart-1.0/bin and a sample BART configuration file in /usr/edb-bart-1.0/etc</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">That’s a very easy installation.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">For more information on configuring BART Host and Database Host, the following are some documents that will help:</span><br />
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">1. pg_basebackup configuration for PostgreSQL:</span></strong><br />
<span style="font-family: Georgia, Times New Roman, serif;">http://www.postgresql.org/docs/current/static/app-pgbasebackup.html</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">2. For direct password less ssh configuration user can refer following link </span></strong><br />
<span style="font-family: Georgia, Times New Roman, serif;">http://www.enterprisedb.com/docs/en/1.0/bart/EDB_Backup_and_Recovery_Tool_Guide-17.htm#P1008_76316</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">After the installation of the BART binaries, the user also has to create a BART configuration file.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">The following is a sample configuration file for BART:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">[BART]
bart-host= enterprisedb@127.0.0.1
backup_path = /opt/backup
pg_basebackup_path = /usr/ppas-9.4/bin/pg_basebackup
logfile = /tmp/bart.log
[PG]
host = 127.0.0.1
port = 5432
user = postgres
description = Postgres server
[PPAS94]
host = 127.0.0.1
port = 5444
user = enterprisedb
description = PPAS 94 server</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">Global Configuration Settings</span></strong></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">Content under the [BART] tag are called global configuration settings. Under this tag are the following:</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">1. bart-host:</strong> the IP address of the host on which BART is installed. The value for this parameter must be specified in the form: bart_user@bart_host_address, where bart_user is the operating system user account on the BART host that is used to run BART and owns the BART backup catalog directory. bart_host_address is the IP address of the BART host.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">2. backup_path:</strong> specifies the file system parent directory where all BART database server base backups and archived WAL files are stored. This parameter is required.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">3. pg_basebackup_path: </strong>specifies the path to the pg_basebackup program of the Postgres database server installed on the BART host.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">4. log file:</strong> specifies the path to the BART log file. This parameter is optional. If no path to a log file is specified after logfile =, or if the parameter is commented out, BART does not create a log file.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">The remaining part of configuration file is self-explanatory. The <strong style="font-style: inherit; line-height: 1.7;">TAG: [PG]/[PPAS94]</strong> part is content for servers which the user wants to back up.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">Pg_basebackup Settings</span></strong></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">After performing the above configuration on the Backup Server, the user has to do set following settings on the servers that they want to back up. Below are the settings for enabling backup using pg_basebackup.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">The user has to set a few parameters in PostgreSQL postgresql.conf file, which he wants to backup:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">1. wal_level parameter to archive or hot_standby.
2. archive_mode=on
3. archive_command setting.
4. max_wal_senders to 1 or more than one, since pg_basebackup uses the replication protocol to copy data directory.</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">For more information on each setting please refer to the following:</span><br />
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">1. wal_level:</span></strong><br />
<span style="font-family: Georgia, Times New Roman, serif;">http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">2. archive_mode and archive_command:</span></strong><br />
<span style="font-family: Georgia, Times New Roman, serif;">http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">3. max_wal_senders:</span></strong><br />
<span style="font-family: Georgia, Times New Roman, serif;">http://www.postgresql.org/docs/9.4/static/runtime-config-replication.html</span><br />
<span style="font-family: Georgia, Times New Roman, serif;">http://www.enterprisedb.com/docs/en/1.0/bart/EDB_Backup_and_Recovery_Tool_Guide-19.htm#TopOfPage</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">With the above settings, the user then needs to update the pg_hba.conf file for the replication connection.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;"><strong style="font-style: inherit; line-height: 1.7;">Note:</strong> The above settings are for pg_basebackup to take backups using replication protocols. In case users need more information about pg_basebackup and settings, please use the above mentioned link</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<strong style="font-style: inherit; line-height: 1.7;"><span style="font-family: Georgia, Times New Roman, serif;">How BART Works</span></strong></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">Now, since we have configured both servers, let’s have a look how BART works.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">The following command executes a backup:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;"> bart -c bart.cfg BACKUP -s ppas94</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">And below is the output:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">[bart@localhost ~]$ bart -c bart.cfg BACKUP -s ppas94
INFO: creating backup for server 'ppas94'
INFO: backup identifier: '1413852137762'
6394456/6394456 kB (100%), 1/1 tablespace
INFO: backup checksum: 7f49ea9653511308710c174f22ec765d
INFO: backup completed successfully
[bart@localhost ~]$ </pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">That was an easy way to take a backup. The DBA can also create a job to execute the above command to take backups.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">If the user wants to list the backup using BART, the user can use the option <strong style="font-style: inherit; line-height: 1.7;">SHOW-BACKUPS</strong>:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">[bart@localhost ~]$ bart -c bart.cfg SHOW-BACKUPS -s ppas94
Server Name Backup ID Backup Time Backup Size
ppas94 1413852137762 2014-10-20 17:43:41 6244.59 MB </pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">This is useful for knowing what backups a user has available for recovery. The above command gives important information:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">1. Backup ID: It’s a unique ID for the physical backup
2. Backup Time: Time when backup was taken
3. Backup Size: Size of backup</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">This information is useful when a user wants to plan for recovery using backup. This way, the user can also plan for disk size.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">Sometimes a user wants to verify their backup state.<strong style="font-style: inherit; line-height: 1.7;"> VERIFY-CHKSUM</strong> option is useful in this case:</span></div>
<pre style="background-color: #f4f4f4; color: #222222; font-family: 'Courier 10 Pitch', Courier, monospace; font-size: 13px; line-height: 1.5; margin-bottom: 1.625em; padding: 0.75em 1.625em;">[bart@localhost ~]$ bart -c bart.cfg VERIFY-CHKSUM -s ppas94 -i 1413852137762
Server Name Backup ID Verify
ppas94 1413852137762 OK</pre>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">I have to say, after putting EDB BART through its paces, I think DBAs will enjoy having such a great tool for making Backup Management easy.</span></div>
<div style="background-color: white; color: #222222; font-size: 14px; line-height: 23px; margin-bottom: 0.825em;">
<span style="font-family: Georgia, Times New Roman, serif;">In my next post, I will blog about the Recovery process.</span></div>
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-32119362177126244032014-06-30T09:05:00.000-07:002014-06-30T10:38:07.147-07:00Switchover/Switchback in PostgreSQL 9.3PostgreSQL 9.3 has two key software updates making switchover/switchback easier in High Availability configurations.<br />
<br />
First, let’s address the software patches and their descriptions:<br />
1. First patch was committed by Fujii Masao.<br />
<pre class="cpp" name="code"> Patch commit# 985bd7d49726c9f178558491d31a570d47340459
</pre>
<br />
With this patch, the walsender process tries to send all outstanding WAL records to the standby in replication when the user shuts down the master.<br />
<br />
This means:<br />
a. All WAL records are synced between two servers after the clean shutdown of the master<br />
b. After promoting the standby to new master, the user can restart the stopped master as new standby without a fresh backup from new master.<br />
<br />
2. Second patch was committed by Heikki Linnakangas in PostgreSQL 9.3.<br />
<pre class="cpp" name="code"> Patch commit# abfd192b1b5ba5216ac4b1f31dcd553106304b19
</pre>
<br />
Before PostgreSQL version 9.3, streaming replication used to stop replicating if the timeline on the primary didn’t match the standby. This generally happens when the user promotes one of the standbys to become the new master. Promoting a standby always results in an increment of timeline ID and after that increment, other standbys will refuse to continue replicating.<br />
<br />
With this patch in PostgreSQL 9.3, the standby asks the primary for any timeline history files that are missing from the standby when it connects – if the standby recovery.conf file has the following setting:<br />
recovery_target_timeline='latest'<br />
<br />
The missing files are sent using a new replication command TIMELINE_HISTORY, and stored in the standby's pg_xlog directory. Using the timeline history files, the standby can follow the latest timeline present in the primary, just as it can follow new timelines appearing in an archive WAL directory.<br />
<br />
Because of above patches, if the user performs the following sequence of steps then switchover/switchback can be easily achieved:<br />
To switchover from Master to Standby, use following steps:<br />
On Master:<br />
1. Use any one of following stop options for clean shutdown.<br />
<pre class="cpp" name="code"> pg_ctl stop --pgdata=data_dire --mode=fast
or
pg_ctl stop --pgdata=data_directory --mode=smart
</pre>
<br />
2. Before promoting standby:<br />
<pre class="cpp" name="code">. Make sure all WAL send by Master applied on Standby. Use following functions to verify it:
* pg_last_xlog_receive_location()
* pg_last_xlog_replay_location()
</pre>
<br />
3. Have the proper archive location and archive_command setting accessible to old Master.<br />
<br />
Following is a presentation I delivered at a recent BPUG Meet (Boston PostgreSQL User Group) to discuss switchover/switchback.<br />
<iframe frameborder="0" height="400" marginheight="0" marginwidth="0" scrolling="no" src="http://www.slideshare.net/slideshow/embed_code/36469258" width="476"></iframe>
<br />
<div>
<br /></div>
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-50851953753113790642014-05-21T07:02:00.000-07:002014-05-21T07:13:42.929-07:00Monitoring approach for Streaming Replication with Hot Standby in PostgreSQL 9.3.The people using PostgreSQL and the Streaming Replication feature seem to ask many of the same questions:<br />
1. How best to monitor Streaming Replication?<br />
2. What is the best way to do that?<br />
3. Are there alternatives, when monitoring on Standby, to using the pg_stat_replication view on Master?<br />
4. How should I calculate replication lag-time, in seconds, minutes, etc.?<br />
<br />
In light of these commonly asked questions, I thought a blog would help. The following are some methods I’ve found to be useful.<br />
<br />
Monitoring is critical for large infrastructure deployments where you have Streaming Replication for:<br />
1. Disaster recovery<br />
2. Streaming Replication is for High Availability<br />
3. Load balancing, when using Streaming Replication with Hot Standby<br />
<br />
PostgreSQL has some building blocks for replication monitoring, and the following are some important functions and views which can be use for monitoring the replication:<br />
<br />
<b>1. pg_stat_replication view on master/primary server.</b><br />
This view helps in monitoring the standby on Master. It gives you the following details:<br />
<br />
<pre class="cpp" name="code"> pid: Process id of walsender process
usesysid: OID of user which is used for Streaming replication.
usename: Name of user which is used for Streaming replication
application_name: Application name connected to master
client_addr: Address of standby/streaming replication
client_hostname: Hostname of standby.
client_port: TCP port number on which standby communicating with WAL sender
backend_start: Start time when SR connected to Master.
state: Current WAL sender state i.e streaming
sent_location: Last transaction location sent to standby.
write_location: Last transaction written on disk at standby
flush_location: Last transaction flush on disk at standby.
replay_location: Last transaction flush on disk at standby.
sync_priority: Priority of standby server being chosen as synchronous standby
sync_state: Sync State of standby (is it async or synchronous).
</pre>
<br />
<br />
e.g.:<br />
<pre class="cpp" name="code">postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+---------------------------------
pid | 1114
usesysid | 16384
usename | repuser
application_name | walreceiver
client_addr | 172.17.0.3
client_hostname |
client_port | 52444
backend_start | 15-MAY-14 19:54:05.535695 -04:00
state | streaming
sent_location | 0/290044C0
write_location | 0/290044C0
flush_location | 0/290044C0
replay_location | 0/290044C0
sync_priority | 0
sync_state | async
</pre>
<br />
<br />
<b>2. pg_is_in_recovery() :</b> Function which tells whether standby is still in recovery mode or not.<br />
e.g.<br />
<pre class="cpp" name="code">postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
</pre>
<br />
<br />
<b>3. pg_last_xlog_receive_location:</b> Function which tells location of last transaction log which was streamed by Standby and also written on standby disk.<br />
e.g.<br />
<pre class="cpp" name="code">postgres=# select pg_last_xlog_receive_location();
pg_last_xlog_receive_location
-------------------------------
0/29004560
(1 row)
</pre>
<br />
<b>4. pg_last_xlog_replay_location: </b>Function which tells last transaction replayed during recovery process. e.g is given below:<br />
<pre class="cpp" name="code">postgres=# select pg_last_xlog_replay_location();
pg_last_xlog_replay_location
------------------------------
0/29004560
(1 row)
</pre>
<br />
<b>5. pg_last_xact_replay_timestamp:</b> This function tells about the time stamp of last transaction which was replayed during recovery. Below is an example:<br />
<pre class="cpp" name="code">postgres=# select pg_last_xact_replay_timestamp();
pg_last_xact_replay_timestamp
----------------------------------
15-MAY-14 20:54:27.635591 -04:00
(1 row)
</pre>
<br />
<br />
Above are some important functions/views, which are already available in PostgreSQL for monitoring the streaming replication.<br />
<br />
So, the logical next question is, “What’s the right way to monitor the Hot Standby with Streaming Replication on Standby Server?”<br />
If you have Hot Standby with Streaming Replication, the following are the points you should monitor:<br />
<b>1. Check if your Hot Standby is in recovery mode or not:</b><br />
For this you can use <span style="font-family: Courier New, Courier, monospace;">pg_is_in_recovery()</span> function.<br />
<br />
<b>2.Check whether Streaming Replication is working or not.</b><br />
And easy way of doing this is checking the <span style="font-family: Courier New, Courier, monospace;">pg_stat_replication</span> view on Master/Primary. This view gives information only on master if Streaming Replication is working.<br />
<br />
<b>3. Check If Streaming Replication is not working and Hot standby is recovering from archived WAL file.</b><br />
For this, either the DBA can use the PostgreSQL Log file to monitor it or utilize the following functions provided in PostgreSQL 9.3:<br />
<br />
<pre class="cpp" name="code">pg_last_xlog_replay_location();
pg_last_xact_replay_timestamp();
</pre>
<br />
<b>4. Check how far off is the Standby from Master</b>.<br />
There are two ways to monitor lag for Standby.<br />
<b><br /></b>
<b> i. Lags in Bytes:</b> For calculating lags in bytes, users can use the <span style="font-family: Courier New, Courier, monospace;">pg_stat_replication </span>view on the master with the function <span style="font-family: Courier New, Courier, monospace;">pg_xlog_location_diff </span>function. Below is an example:<br />
<br />
<pre class="cpp" name="code">pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location)
</pre>
<br />
which gives the lag in bytes.<br />
<br />
<b> ii. Calculating lags in Seconds.</b> The following is SQL, which most people uses to find the lag in seconds:<br />
<pre class="cpp" name="code"> SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;
</pre>
<br />
Including the above into your repertoire can give you good monitoring for PostgreSQL.<br />
<br />
I will in a future post include the script that can be used for monitoring the Hot Standby with PostgreSQL streaming replication.<br />
<br />Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-4648998299907166402014-05-15T14:52:00.002-07:002014-05-16T11:24:01.234-07:00Write Operation: MongoDB Vs PostgreSQL 9.3 (JSON)<b>PostgreSQL 9.3</b> has lot of new improvement like the addition of new operators for JSON data type in postgreSQL, that prompted me to explore its features for NoSQL capabilities.<br />
<br />
MongoDB is one of NoSQL solutions that have gotten a great deal of attention in the NoSQL market. So, this time I thought to do some benchmarking with the NoSQL capability of JSON in MongoDB and the JSON datatype in PostgreSQL 9.3<br />
<br />
For this benchmark, I have used the same machine with no optimization in installation of PostgreSQL and MongoDB (since I wanted to see how things work, out of box with default installation). And I used the sample data from MongoDB's site, around which I had developed the functions which can generate random data using the same sample for Mongo and for PostgreSQL.<br />
<br />
<b>In this benchmarking, I have verified following:</b><br />
1. PostgreSQL COPY Vs Mongo-Import<br />
2. Data Disk Size of PostgreSQL and Mongo for same amount of data.<br />
3. PostgreSQL INSERT Vs Mongo Insert<br />
<br />
<b>Some specification before I would display the result:</b><br />
1. Operating System: CentOS 6.5, 64 bit.<br />
2. Total Memory: 1.538 GB<br />
3. MongoDB version: 2.4.9<br />
4. PostgreSQL: 9.3<br />
<br />
Below is the results which I have got:<br />
<br />
<b>For Bulkload (COPY Vs MongoImport):</b><br />
<br />
<pre class="cpp" name="code"># of rows 1000 10000 100000 1000000
mongo-import (ms) 86.241679 569.761325 6940.837053 68610.69793
PG COPY (ms) 27.36344 176.705094 1769.641917 24801.23291
</pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwG_Wc6vUcwAViCUB2YFC2c-PLMQQpoo5bzV8zT3uPA2eDLRdXhy3G-FkiXQ8F_TnNYpT_kSR5dxg5yKOs5wRIoVuP-5p5Peccm_boNgpV-VqPOotY4iTbZ4imYdQjyX8dtpAQXb1VhvPk/s1600/Screen+Shot+2014-05-15+at+5.39.59+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwG_Wc6vUcwAViCUB2YFC2c-PLMQQpoo5bzV8zT3uPA2eDLRdXhy3G-FkiXQ8F_TnNYpT_kSR5dxg5yKOs5wRIoVuP-5p5Peccm_boNgpV-VqPOotY4iTbZ4imYdQjyX8dtpAQXb1VhvPk/s1600/Screen+Shot+2014-05-15+at+5.39.59+PM.png" height="274" width="640" /></a></div>
<br />
<b>Disk space utilization:</b><br />
<b><br /></b>
<br />
<pre class="cpp" name="code"># of rows 1000 10000 100000 1000000
mongo disks (mb) 208 208 208.2033236 976
pg size (mb) 0.3515625 3.2890625 32.71875 326.8984375
</pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWuTryprLUD_cp5zav-wKvsKaEuLfhmqK5UoZTIqWwUu-NdoGV75se-pn34-k49NN9Sklkh0JcJfdSBoOfUkGafvvg5JNStxq6aPFiSXWhC_nTwfV6rHc7Ldn2LRjiKqvPbNGXP1KbObRo/s1600/Screen+Shot+2014-05-15+at+5.40.19+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWuTryprLUD_cp5zav-wKvsKaEuLfhmqK5UoZTIqWwUu-NdoGV75se-pn34-k49NN9Sklkh0JcJfdSBoOfUkGafvvg5JNStxq6aPFiSXWhC_nTwfV6rHc7Ldn2LRjiKqvPbNGXP1KbObRo/s1600/Screen+Shot+2014-05-15+at+5.40.19+PM.png" height="274" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<br />
<b>For INSERTs:</b><br />
<b><br /></b>
<br />
<pre class="cpp" name="code"># of Inserts 1000 10000 100000 1000000
MONGO INSERTS (sec) 0.521397404 4.578372454 43.92753611 449.4023542
PG INSERTS (sec) 0.326254529 4.169742939 32.21799302 319.2562722
</pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-Ou2VTSVjhys/U3U2N4OJ1mI/AAAAAAAAAY4/DNgiN4ssFH8/s1600/Screen+Shot+2014-05-15+at+5.40.09+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-Ou2VTSVjhys/U3U2N4OJ1mI/AAAAAAAAAY4/DNgiN4ssFH8/s1600/Screen+Shot+2014-05-15+at+5.40.09+PM.png" height="284" width="640" /></a></div>
<br />
If you look at above stats, you can see PostgreSQL JSON is much better in bulk loading and INSERTs.<br />
<br />
Best thing is that it takes less space than MongoDB and doesn't eat up much disk space.<br />
<div>
<br /></div>
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-16968079780479164512013-11-20T18:53:00.000-08:002013-11-21T13:53:35.406-08:00Postgres Plus Advanced Server 9.3 Features<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
<strong>Postgres Plus Advanced Server 9.3</strong>, released on Tuesday for general availability, contains all of the innovation that was part of PostgreSQL 9.3, which was released in September.<br />
These features include the following:</div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
<strong>1. Configuration directive 'include_dir'</strong><br />
<strong>2. COPY FREEZE for more efficient bulk loading</strong><br />
<strong>3. Custom Background Workers</strong><br />
<strong>4. Data Checksums</strong><br />
<strong>5. JSON: Additional functionality</strong><br />
<strong>6. LATERAL JOIN</strong><br />
<strong>7. Parallel pg_dump for faster backups</strong><br />
<strong>8. 'pg_isready' server monitoring tool</strong><br />
<strong>9. Switch to Posix shared memory and mmap()</strong><br />
<strong>10. Event Triggers</strong><br />
<strong>11. VIEW Features:</strong><br />
<strong> Materialized Views</strong><br />
<strong> Recursive View Syntax</strong><br />
<strong> Updatable Views</strong><br />
<strong>12. Writeable Foreign Tables</strong><br />
<strong> postgres_fdw</strong><br />
<strong>13. Replication Improvements</strong><br />
<strong> Streaming-Only Remastering</strong><br />
<strong> Fast Failover</strong><br />
<strong> Architecture-Independent Streaming</strong><br />
<strong> pg_basebackup conf setup</strong></div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
For Postgres Plus Advanced Server, we integrated into the core PostgreSQL additional performance improvements, new packages and Object oriented features so that our database can address a wider range of enterprise use cases.</div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
Partitioning enhancements to boost performance for INSERTS/UPDATES/SELECT was a major development for Postgres Plus. Below are graphs illustrating the performance increases of Postgres Plus Advanced Server 9.3 compared to the 9.2 version.</div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
<a data-mce-href="http://vibhorkumar.files.wordpress.com/2013/11/tps_select.png" href="http://vibhorkumar.files.wordpress.com/2013/11/tps_select2.png" style="background-color: white; text-align: center;"></a><a data-mce-href="http://vibhorkumar.files.wordpress.com/2013/11/tps_select2.png" href="http://vibhorkumar.files.wordpress.com/2013/11/tps_select2.png"><img alt="TPS_Select" class="aligncenter wp-image-481" data-mce-src="http://vibhorkumar.files.wordpress.com/2013/11/tps_select2.png" height="270" src="http://vibhorkumar.files.wordpress.com/2013/11/tps_select2.png" style="border: 0px; cursor: default; display: block; margin-left: auto; margin-right: auto;" width="392" /></a></div>
<div>
<div data-mce-style="text-align: center;" style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em; text-align: center;">
Figure: TPS SELECT</div>
<div data-mce-style="text-align: center;" style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em; text-align: center;">
<a data-mce-href="http://vibhorkumar.files.wordpress.com/2013/11/tps_update2.png" href="http://vibhorkumar.files.wordpress.com/2013/11/tps_update.png"></a><a data-mce-href="http://vibhorkumar.files.wordpress.com/2013/11/tps_update2.png" href="http://vibhorkumar.files.wordpress.com/2013/11/tps_update2.png" style="background-color: white;"><img alt="TPS_Update" class="aligncenter wp-image-482" data-mce-src="http://vibhorkumar.files.wordpress.com/2013/11/tps_update2.png" height="263" src="http://vibhorkumar.files.wordpress.com/2013/11/tps_update2.png" style="border: 0px; cursor: default; display: block; margin-left: auto; margin-right: auto;" width="392" /></a></div>
<div data-mce-style="text-align: center;" style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em; text-align: center;">
Figure TPS UPDATE</div>
<div data-mce-style="text-align: left;" style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
<span data-mce-style="line-height: 1.5;" style="line-height: 1.5;">The release features some important functions that can make a developer’s life easier, such as:</span></div>
<div data-mce-style="text-align: left;" style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
1. <strong>DBMS_RANDOM</strong> package. This packages helps users to create Random numbers, Random strings and Random dates. PostgreSQL supports random functions, which enable users to have their own function on top of random for random string and random date.</div>
<div data-mce-style="text-align: left;" style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
With this package, users can easily use the built-in functions for those two purposes. Below are some examples:<br />
DBMS_RANDOM package can be used to easily generate random strings and dates, and users don’t need to make their own wrapper function for these two activities. Below is one simple example of using it.</div>
<pre class="cpp" name="code">user=# BEGIN
user$# DBMS_OUTPUT.put_line('Run 1 : seed=0');
user$# DBMS_RANDOM.seed (val => 0);
user$# FOR i IN 1 ..5 LOOP
user$# DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
user$# END LOOP;
user$#
user$# DBMS_OUTPUT.put_line('Run 2 : seed=0');
user$# DBMS_RANDOM.seed (val => 0);
user$# FOR i IN 1 ..5 LOOP
user$# DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
user$# END LOOP;
user$#
user$# END;
Run 1 : seed=0
i=1 : value=6.756044853478672
i=2 : value=7.806749390438203
i=3 : value=1.1173334638588129
i=4 : value=5.221515491604802
i=5 : value=8.445010517258194
Run 2 : seed=0
i=1 : value=6.756044853478672
i=2 : value=7.806749390438203
i=3 : value=1.1173334638588129
i=4 : value=5.221515491604802
i=5 : value=8.445010517258194
EDB-SPL Procedure successfully completed
user=# BEGIN
user$# FOR i IN 1 .. 5 LOOP
user$# DBMS_OUTPUT.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10));
user$# END LOOP;
user$# END;
string('x',10)= 1TT23XR8X2
string('x',10)= DO5D2KUUVD
string('x',10)= AGNPAXDECT
string('x',10)= 7JC6RMU9KX
string('x',10)= 13BW6JM6KN
EDB-SPL Procedure successfully completed
user=# BEGIN
user$# FOR i IN 1 .. 5 LOOP
user$# DBMS_OUTPUT.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)));
user$# END LOOP;
user$# END;
date= 20-JUN-14 00:00:00
date= 26-MAY-14 00:00:00
date= 11-JAN-14 00:00:00
date= 27-JUN-14 00:00:00
date= 21-DEC-13 00:00:00
EDB-SPL Procedure successfully completed
</pre>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
2. <strong>DBMS_LOCK.sleep</strong>: Similar to pg_sleep function, the DBMS_LOCK.sleep package is meant for Oracle users/developers who are more familiar with Oracle packages.</div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
3. <strong>DBMS_CRYPTO:</strong> DMBMS_CRYPTO is new in Postgres Plus Advanced Server 9.3 and it provides the interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs for running network communications. It provides support for several industry-standard encryption and hashing algorithms.</div>
<pre class="cpp" name="code">DECLARE
input_string VARCHAR2 (200) := 'Secret Message';
output_string VARCHAR2 (200);
encrypted_raw RAW (2000); -- stores encrypted binary text
decrypted_raw RAW (2000); -- stores decrypted binary text
num_key_bytes NUMBER := 256/8; -- key length 256 bits (32 bytes)
key_bytes_raw RAW (32); -- stores 256-bit encryption key
encryption_type INTEGER := -- total encryption type
DBMS_CRYPTO.ENCRYPT_DES
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes::INTEGER);
encrypted_raw := DBMS_CRYPTO.ENCRYPT
(
src => convert_to(input_string, 'LATIN1'),
typ => encryption_type,
key => key_bytes_raw
);
-- The encrypted value "encrypted_raw" can be used here
decrypted_raw := DBMS_CRYPTO.DECRYPT
(
src => encrypted_raw,
typ => encryption_type,
key => key_bytes_raw
);
output_string := convert_from(decrypted_raw,'LATIN1');
DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
END;
</pre>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
4. <strong>DBMS_SCHEDULER:</strong> With Postgres Plus 9.2, we got DBMS_JOB package, and now Postgres Plus 9.3 has come out with DBMS_SCHEDULER, which has more control options for jobs and better visibility of scheduled jobs.<br />
5. <strong>UTL_ENCODE:</strong> This is another important package for keeping sensitive information in the database. Using this package, users can easily encode and decode their data and keep it in database. This package can be utilized in new applications, and gives flexibility around important encode/decode functions for developers and users where data is very important. This is particularly useful for users working applications that were written for Oracle but migrated onto Postgres Plus Advanced Server. .</div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
Let’s see an example:</div>
<pre class="cpp" name="code">user=# DECLARE
user-# v_str VARCHAR2(100);
user$# BEGIN
user$# --generate encoded value
user$# v_str := utl_encode.text_encode('EnterpriseDB','gb18030', UTL_ENCODE.BASE64);
user$# dbms_output.put_line('Encoded string => '||v_str);
user$#
user$# --take the encoded value and decode it
user$# v_str := utl_encode.text_decode(v_str,'gb18030', UTL_ENCODE.BASE64);
user$# dbms_output.put_line('Decoded string => '||v_str);
user$# END;
Encoded string => RW50ZXJwcmlzZURC
Decoded string => EnterpriseDB
EDB-SPL Procedure successfully completed
</pre>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
6. <strong>UTL_HTTP:</strong> Advanced Server 9.3 has an UTL_HTTP package. This package provides functions for HTTP callouts from SQL/SPL. Developers/users can use this package and associated functions for accessing data on the Internet over HTTP.</div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
7.<strong> UTL_URL:</strong> This is one of additions we developed for Postgres Plus 9.3. This package has two functions, escape and unescape mechanisms for URL characters. The escape function helps to escape a URL before the URL can be used to fetch data from a website. The unescape function can unescape any escaped character used in the URL, before fetching the data from a website. These two packages with UTL_HTTP allow users to direct the fetching of data from a website without having to write complex code in the application for handling specific data from a website.</div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
8. New in <strong>EDB*loader.</strong> In Postgres Plus 9.3 EDB*loader has more control options for bulk loading.<br />
a. ROWS parameter: prior to version 9.3, EDB*Loader processed entire data files as a single transaction. With parameter, users can control the processing of large amounts of data after which COMMIT needs to be executed. More control for processing/Loading data files.<br />
b. Error Codes: EDB*loader now supports some additional exit/error codes, which will help users/developers include proper exit code checking while using a higher speed bulk data loader with parallel processing. Exit codes are shown below:</div>
<pre class="cpp" name="code"> 0: Success
1: Failure
2: Warning
3: Fatal
</pre>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
c. <strong>Streaming output files to client:</strong> Prior to version 9.3, users had to check the logfile of EDB*loader on the server side. Files: logfile, Bad file and discard file used to be created on the server side, and for troubleshooting users had to log in on the server to verify these files. Now, Postgres Plus 9.3 enables these files to be created at the client site. Users are no longer required to log into the server to verify/check these files..<br />
d. New GUC for empty string. EDB*loader now has one GUC which users can utilize to control the default behavior of an empty_string in their datafile. edbldr.empty string has the following valid values:<br />
1. null: empty field is treated as a null if the raw field contains no characters or a pair of delimiters with nothing in between.<br />
2. empty_string: empty field is treated as a empty_string, if the raw field contains no characters or a pair of delimiters with nothing in between.<br />
3. pgsql: empty field is treated as a null if the raw field contains no characters, but as an empty string if it contains a pair of delimiters with nothing in between.<br />
Default is pgsql.</div>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
9. <strong>New REGEXP functions.</strong> Postgres Plus 9.3 nowhas three new REGEXP functions for developers.<br />
a. REGEXP_COUNT: This searches a string for a regular expression, and returns a count of the times that the regular expression occurs. Here’s a simple example:</div>
<pre class="cpp" name="code"> user=# SELECT REGEXP_COUNT('reinitializing', 'i', 1);
regexp_count
--------------
5
(1 row)
</pre>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
b. <strong>REGEXP_INSTR:</strong> This function searches a string for a POSIX-style regular expression and returns the position within the string where the match was located.</div>
<pre class="cpp" name="code">user=# SELECT REGEXP_INSTR('800-555-1212', '[0-9][0-9][0-9]', 1, 1) ;
regexp_instr
--------------
1
(1 row)
</pre>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
c. <strong>REGEXP_SUBSTR:</strong> This function searches a string for a pattern specified by a POSIX compliant regular expression and returns the string that matches the pattern specified in the call to the function.</div>
<pre class="cpp" name="code">user=# SELECT REGEXP_SUBSTR('800-555-1212', '[0-9][0-9][0-9]', 1, 2);
regexp_substr
---------------
555
(1 row)
</pre>
<div style="color: #333333; font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 14px; line-height: 21px; margin-bottom: 1.3em;">
10. <strong>New exception codes for UTL_FILE package:</strong> The UTL_FILE package provides the capability to read from, and write to files on the operating system’s file system. To provide error control capabilities to developers/users, Postgres Plus 9.3 has added some new exception codes, which users can utilize in their PL/SPL code for better exception handling. One simple example is given below:</div>
<pre class="cpp" name="code">user=# CREATE DIRECTORY tempdir_fb22954 AS '/tmp/';
CREATE DIRECTORY
user=# SELECT dirname
user-# FROM edb_dir
user-# WHERE dirname='tempdir_fb22954';
dirname
-----------------
tempdir_fb22954
(1 row)
user=#
user=# -- check "utl_file.invalid_operation" exception
user=# DECLARE v_testfile UTL_FILE.FILE_TYPE;
user$#
user$# v_directory VARCHAR2(50) := 'tempdir_fb22954';
user$#
user$# v_filename VARCHAR2(50) := 'test_file_exist.txt';
user$#
user$# BEGIN -- Create file
user$# v_testfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
user$#
user$# UTL_FILE.PUT(v_testfile,'A');
user$#
user$# UTL_FILE.NEW_LINE(v_testfile);
user$#
user$# UTL_FILE.FCLOSE(v_testfile);
user$#
user$# DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
user$#
user$# -- It should throw exception because file is open for read.
user$# v_testfile := UTL_FILE.FOPEN(v_directory,v_filename,'r');
user$#
user$# UTL_FILE.PUT(v_testfile,'B');
user$#
user$# UTL_FILE.NEW_LINE(v_testfile);
user$#
user$# UTL_FILE.PUT(v_testfile,'C');
user$#
user$# UTL_FILE.NEW_LINE(v_testfile);
user$#
user$# exception
user$# WHEN utl_file.invalid_operation THEN
user$# RAISE notice 'exception caught utl_file.invalid_operation : SQLERRM: %',
user$# sqlerrm;
user$# UTL_FILE.FCLOSE(v_testfile);
user$# WHEN others THEN
user$# RAISE notice 'exception, others : SQLERRM: %',
user$# sqlerrm;
user$# UTL_FILE.FCLOSE(v_testfile);
user$# END;
Created file: test_file_exist.txt
NOTICE: exception caught utl_file.invalid_operation : SQLERRM: invalid file operation
EDB-SPL Procedure successfully completed
</pre>
</div>
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-24364397653728077392013-08-12T12:42:00.000-07:002013-08-12T12:42:51.274-07:00New in PostgreSQL 9.3: Data Types In series of writing further on upcoming features in 9.3, I thought about including new improvements coming in data types in PostgreSQL.
<br />
<b>1. Increase the maximum length of large objects from 2GB to 4TB </b>
<br />
<b><br /></b>
PostgreSQL has support of Large Objects from starting. However the limit of large objects in PostgreSQL was limited to 2GB.
<br />
<br />
From 9.3 onwards, PostgreSQL can store large objects up to 4TB. Thats happened due to lifting the limitation of API for large object. APIs like lo_seek(),lo_tell cannot return over 2GB offset and main reason was offset parameters defined for these function is of 4 bytes and results length provided by these functions is 4 bytes. If user do the calculation, he can see 2^31-1=2GB, it resulted in 2GB.
<br />
<br />
To overcome from this limitation, PostgreSQL is coming with new APIs: lo_seek64 and lo_tell64 functions. Libpq interface will check if those lo_tell64/seek64 exits then use it or use the 32 bit of lo_seek/lo_tell functions, this way compatibility has been maintained in PostgreSQL for older release and new upcoming release.<br />
<br />
This is good add-on for PostgreSQL. Thanks to Tatsuo Ishii (pgpool developer and developer for this add-on).
<br />
<br />
<b>2. Text timezone designations using ISO "T" function for timestamptz </b>
<br />
This is new for timestamtz. In 9.3, text timezone designation is allowed using ISO "T" format. This was not working in pre-9.3. Lets look at one example:
<br />
<b> In pre-9.3 </b>
<br />
<pre class="cpp" name="code">worktest=# select '2011-08-29T09:11:14.123 America/Chicago'::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "2011-08-29T09:11:14.123 America/Chicago"
LINE 1: select '2011-08-29T09:11:14.123 America/Chicago'::timestampt...
</pre>
<br />
As user can see, pre-9.3 has complained about it. However in <b> In 9.3 </b> this has been fixed.
<br />
<pre class="cpp" name="code">pgsqltest=# select '2011-08-29T09:11:14.123 America/Chicago'::timestamptz;
timestamptz
----------------------------
2011-08-29 10:11:14.123-04
(1 row)
</pre>
<br />
Good to see this fix.
<br />
<br />
<b>3. New operators and Functions for JSON data strings </b>
<br />
PostgreSQL 9.3, is coming withe some new functions and operators for JSON data types, which is add-ons for users who uses JSON data type in their application. Now, they can explore new functions and operators for their use case.
Pre-9.3, had following functions:
<br />
<br />
<pre class="cpp" name="code">array_to_json
row_to_json
</pre>
<br />
In 9.3, we have following new operators:
<br />
<table border="1" class="CALSTABLE">
<colgroup><col></col>
<col></col>
<col></col>
<col></col>
</colgroup><thead>
<tr>
<th>Operator</th>
<th>Right Operand Type</th>
<th>Description</th>
<th>Example</th>
</tr>
</thead>
<tbody>
<tr>
<td><tt class="LITERAL">-></tt></td>
<td>int</td>
<td>Get JSON array element</td>
<td><tt class="LITERAL">'[1,2,3]'::json->2</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">-></tt></td>
<td>text</td>
<td>Get JSON object field</td>
<td><tt class="LITERAL">'{"a":1,"b":2}'::json->'b'</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">->></tt></td>
<td>int</td>
<td>Get JSON array element as text</td>
<td><tt class="LITERAL">'[1,2,3]'::json->>2</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">->></tt></td>
<td>text</td>
<td>Get JSON object field as text</td>
<td><tt class="LITERAL">'{"a":1,"b":2}'::json->>'b'</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">#></tt></td>
<td>array of text</td>
<td>Get JSON object at specified path</td>
<td><tt class="LITERAL">'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">#>></tt></td>
<td>array of text</td>
<td>Get JSON object at specified path as text</td>
<td><tt class="LITERAL">'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'</tt></td>
</tr>
</tbody>
</table>
<br />
And following new functions:
<br />
<br />
<table border="1" class="CALSTABLE">
<colgroup><col></col>
<col></col>
<col></col>
<col></col>
<col></col>
</colgroup><thead>
<tr>
<th>Function</th>
<th>Return Type</th>
<th>Example</th>
</tr>
</thead>
<tbody>
<tr>
<td><tt class="LITERAL">to_json(anyelement)</tt></td>
<td><tt class="TYPE">json</tt></td>
<td><tt class="LITERAL">to_json('Fred said
"Hi."'::text)</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_array_length(json)</tt></td>
<td><tt class="TYPE">int</tt></td>
<td><tt class="LITERAL">json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_each(json)</tt></td>
<td><tt class="TYPE">SETOF key text, value json</tt></td>
<td><tt class="LITERAL">select * from
json_each('{"a":"foo", "b":"bar"}')</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_each_text(from_json
json)</tt></td>
<td><tt class="TYPE">SETOF key text, value text</tt></td>
<td><tt class="LITERAL">select * from
json_each_text('{"a":"foo", "b":"bar"}')</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_extract_path(from_json json,
VARIADIC path_elems text[])</tt></td>
<td><tt class="TYPE">json</tt></td>
<td><tt class="LITERAL">json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_extract_path_text(from_json
json, VARIADIC path_elems text[])</tt></td>
<td><tt class="TYPE">text</tt></td>
<td><tt class="LITERAL">json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4',
'f6')</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_object_keys(json)</tt></td>
<td><tt class="TYPE">SETOF text</tt></td>
<td><tt class="LITERAL">json_object_keys('{"f1":"abc","f2":{"f3":"a",
"f4":"b"}}')</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_populate_record(base
anyelement, from_json json, [, use_json_as_text
bool=false]</tt></td>
<td><tt class="TYPE">anyelement</tt></td>
<td><tt class="LITERAL">select * from
json_populate_record(null::x, '{"a":1,"b":2}')</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_populate_recordset(base
anyelement, from_json json, [, use_json_as_text
bool=false]</tt></td>
<td><tt class="TYPE">SETOF anyelement</tt></td>
<td><tt class="LITERAL">select * from
json_populate_recordset(null::x,
'[{"a":1,"b":2},{"a":3,"b":4}]')</tt></td>
</tr>
<tr>
<td><tt class="LITERAL">json_array_elements(json)</tt></td>
<td><tt class="TYPE">SETOF json</tt></td>
<td><tt class="LITERAL">json_array_elements('[1,true,
[2,false]]')</tt></td>
</tr>
</tbody>
</table>
<br />
<br />
<b>4. New functions to support hstore to JSON </b>
<br />
<b><br /></b>
PostgreSQL 9.3, is also coming with new functions for converting values of hstore to JSON.
Following are new functions with example which is coming in for hstore to JSON data types.
<br />
<br />
<table border="1" class="CALSTABLE">
<colgroup><col></col>
<col></col>
<col></col>
<col></col>
<col></col>
</colgroup><thead>
<tr>
<th>Function</th>
<th>Return Type</th>
<th>Description</th>
<th>Example</th>
<th>Result</th>
</tr>
</thead>
<tbody>
<tr>
<td><code class="FUNCTION">hstore_to_json(hstore)</code></td>
<td><tt class="TYPE">json</tt></td>
<td>get <tt class="TYPE">hstore</tt> as a <tt class="TYPE">json</tt> value</td>
<td><tt class="LITERAL">hstore_to_json('"a key"=>1,
b=>t, c=>null, d=>12345, e=>012345,
f=>1.234, g=>2.345e+4')</tt></td>
<td><tt class="LITERAL">{"a key": "1", "b": "t", "c":
null, "d": "12345", "e": "012345", "f": "1.234", "g":
"2.345e+4"}</tt></td>
</tr>
<tr>
<td><code class="FUNCTION">hstore_to_json_loose(hstore)</code></td>
<td><tt class="TYPE">json</tt></td>
<td>get <tt class="TYPE">hstore</tt> as a <tt class="TYPE">json</tt> value, but attempting to distinguish
numerical and Boolean values so they are unquoted in
the JSON</td>
<td><tt class="LITERAL">hstore_to_json_loose('"a
key"=>1, b=>t, c=>null, d=>12345,
e=>012345, f=>1.234, g=>2.345e+4')</tt></td>
<td><tt class="LITERAL">{"a key": 1, "b": true, "c":
null, "d": 12345, "e": "012345", "f": 1.234, "g":
2.345e+4}</tt></td>
</tr>
</tbody></table>
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-52100840166357417352013-08-04T15:20:00.000-07:002013-08-04T15:20:26.155-07:00New in PostgreSQL 9.3: New in FunctionsIn the series of blogging about new features in 9.3, today, I thought about blogging new functions and improvements coming in PostgreSQL.
<br />
Lets look whats new in 9.3, in terms of in build functions:
<br />
<br />
<b> 1. New in one array functions for one dimensional array </b>
<br />
PostgreSQL 9.3, is coming with new functions which can help users to manipulate one dimensional arrays by calling simple functions at the place of crafting their own functions and following some methods to do the modification in it.
<br />
<br />
<b>i. array_remove function </b>
<br />
<br />
This is a new function added in 9.3, which provides ability for removing the elements from array. Function takes <b>two arguments</b>:<br />
<br />
<b>a. One dimensional array from which user wants to remove elements </b><br />
<b> b. element value which user wants to remove </b>.
<br />
<b><br /></b>
<b>Syntax of this function is given below:</b>
<br />
<pre class="cpp" name="code">
ARRAY_REMOVE(<one array="" dimensional="">, element)
</one></pre>
<br />
Example of array_remove is given below:
<br />
<pre class="cpp" name="code">
postgres=# select array_remove(ARRAY['First','Second','Delete','Four'],'Delete');
array_remove
---------------------
{First,Second,Four}
(1 row)
</pre>
<br />
<br />
<b>ii. array_replace function</b>
<br />
<br />
This is a new in 9.3, this helps user to replace any element in array. This function can be use multi-dimensional array Or for single dimensional array.
Example of array_replace is given below:
<br />
<pre class="cpp" name="code">
pgsqltest=# select array_replace(ARRAY[ARRAY[1,2],ARRAY[3,4],ARRAY[56,6],ARRAY[7,8]],56,5);
array_replace
---------------------------
{{1,2},{3,4},{5,6},{7,8}}
(1 row)
</pre>
<br />
<br />
<b> 2. VARIADIC-labeled arguments expansion for concat and format functions </b>
<br />
<br />
This is new addition to concat and format function. pre-9.3 concat and format, function used to ignore VARIADIC label and doesn't used to print right output. However, this has been fixed in 9.3. Lets look at the example of pre-9.3 and in 9.3
<br />
<b><br /></b>
<b> In pre-9.3</b>
<br />
<pre class="cpp" name="code">
worktest=# select concat(variadic array[1,2,3]);
concat
---------
{1,2,3}
(1 row)
</pre>
<br />
<b> In 9.3 </b>
<br />
<pre class="cpp" name="code">
pgsqltest=# select concat(variadic array[1,2,3]);
concat
--------
123
(1 row)
</pre>
<br />
You can see above in 9.3, using VARIADIC label working properly.
<br />
<br />
Lets look at the format() function:
<br />
<b><br /></b>
<b> In pre-9.3 </b>
<br />
<pre class="cpp" name="code">
worktest=# select format(string_agg('%s',','), variadic array_agg(i)) from generate_series(1,3) g(i);
ERROR: too few arguments for format
</pre>
<br />
woow, its error out. However, this is fixed in 9.3 :-).
<br />
<b><br /></b>
<b> In 9.3 </b>
<br />
<pre class="cpp" name="code">
pgsqltest=# select format(string_agg('%s',','), variadic array_agg(i)) from generate_series(1,3) g(i);
format
--------
1,2,3
(1 row)
</pre>
<br />
<br />
<b>3. Improvement in format() function to handle field width and left/right alignment </b>
<br />
This new added in 9.3 format() function, which is going to increase the usability of format() function for developers.<br />
In 9.3, format function is coming with following enhancements:<br />
<b>i). Proper handling of field width.</b><br />
<b>ii). Proper handling of Left/right alignment.</b><br />
<b><br /></b>
Above two improvement was missing in pre-9.3. However, addition of above improvement made format() function to be follower of sprintf() C function.
<br />
Lets look at the example:<br />
<b><br /></b>
<b>In pre-9.3, </b>using of field width resulted into error message, as given below
<br />
<pre class="cpp" name="code">
ERROR: unterminated conversion specifier
worktest=# select format('>>%10s<<', 'Hello');
ERROR: unterminated conversion specifier
worktest=#
</pre>
<br />
However, <b>9.3</b> is coming with proper field width support.
<br />
<pre class="cpp" name="code">
pgsqltest=# select format('>>%10s<<', 'Hello');
format
----------------
>> Hello<<
(1 row)
</pre>
<br />
9.3 is also coming proper handling left/right alignment. Examples are given below:<br />
<b><br /></b>
<b> Left alignment</b>
<br />
<pre class="cpp" name="code">
pgsqltest=# select format('>>%-10s<<', 'Hello');
format
----------------
>>Hello <<
(1 row)
</pre>
<br />
<b> Right alignment </b>
<br />
<pre class="cpp" name="code">
pgsqltest=# select format('>>%1$10s<<', 'Hello');
format
----------------
>> Hello<<
(1 row)
</pre>
<br />
<br />
<b>4. Proper handling of Negative century in to_char, to_date and to_timestamp functions </b>
<br />
<br />
In pre-9.3, following function behavior for negative century was wrong or inconsistent
a. to_char
b. to_date
c. to_timestamp.
<br />
<br />
However, 9.3 is coming with proper fix/handling for negative century. Lets look at the output in pre-9.3 and in 9.3 for each functions.
<br />
<b><br /></b>
<b> pre-9.3 </b>
<br />
<pre class="cpp" name="code">
worktest=# select to_timestamp('4713-01-08 BC','YYYY-DD-MM bc');
to_timestamp
------------------------------
01-AUG-13 00:00:00 -04:56:02
(1 row)
</pre>
<br />
Above you can see that its displaying wrong result for BC.<br />
<b><br /></b>
<b>In 9.3</b>
<br />
<pre class="cpp" name="code">
pgsqltest=# select to_timestamp('4713-01-08 BC','YYYY-DD-MM bc');
to_timestamp
---------------------------------
4713-08-01 00:00:00-04:56:02 BC
(1 row)
</pre>
<br />
Lets see for to_date functions.<br />
<b><br /></b>
<b> In 9.2/pre-9.3 </b>
<br />
<pre class="cpp" name="code">
worktest=# SELECT to_date('4713-01-08 BC','YYYY-DD-MM bc');
ERROR: full year must be between -4712 and +9999, and not be 0
worktest=#
</pre>
<br />
<b> In 9.3 </b>
<br />
<pre class="cpp" name="code">
pgsqltest=# SELECT to_date('4713-01-08 BC','YYYY-DD-MM bc');
to_date
---------------
4713-08-01 BC
(1 row)
</pre>
<br />
Above, you can see in 9.3,to_date function is working right. However to_date in 9.2 didn't able to handle it. Similar behavior you can see for to_char function.
<br />
<br />
<b>5. Improvement in pg_get_viewdef() to print new line after each SELECT and FROM entry </b>
<br />
<br />
This is kind of enhancement made in pg_get_viewdef function of 9.3. And user will be able to see this enhancement in pg_dump plain dump too.
<br />
<br />
This enhancement is more like readability of output of view definition in 9.3 and reducing the line length of view definition. Lets look at how this improvement makes user experience of getting/viewing view definition much better.
<br />
<b><br /></b>
<b>Before 9.3 or in pre-9.3</b>
<br />
<pre class="cpp" name="code">
worktest=# select pg_get_viewdef('pg_tables'::regclass);
pg_get_viewdef
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner,
t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS
hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_
tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");
(1 row)
</pre>
<br />
Above you can see that pg_get_viewdef has printed defintion of view in one line, which not only make readability of view definition difficult. However also increase the line length.
<br />
<br />
<b>Lets look at the pg_get_viewdef in 9.3</b>
<br />
<pre class="cpp" name="code">
pgsqltest=# select pg_get_viewdef('pg_tables'::regclass);
pg_get_viewdef
--------------------------------------------------------------
SELECT n.nspname AS schemaname, +
c.relname AS tablename, +
pg_get_userbyid(c.relowner) AS tableowner, +
t.spcname AS tablespace, +
c.relhasindex AS hasindexes, +
c.relhasrules AS hasrules, +
c.relhastriggers AS hastriggers +
FROM ((pg_class c +
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) +
LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))+
WHERE (c.relkind = 'r'::"char");
(1 row)
</pre>
<br />
which seems me better in terms of readability and doesn't have long line.
<br />
<br />
Enjoy!!
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-44500208944390034322013-07-30T10:22:00.003-07:002013-07-30T10:27:09.996-07:00New in PostgreSQL 9.3: Server Side languagesIn series of blogging about new features coming in PostgreSQL 9.3, I thought to blog about the server side language improvements in PostgreSQL. Lets see whats coming in server side language.
<br />
As PostgreSQL user, you know, PostgreSQL supports multiple server side language. In 9.3, there are some interesting features are coming. Lets look at the new improvements about to come.
<br />
<br />
<b>1. SPI access to number of rows processed by COPY command.</b>
<br />
<b><br /></b>
This is more like new feature which is introduced in 9.3. Before 9.3, this feature was missing. i.e if user uses COPY command inside the plperl/plpython functions, then there was no way inside the procedure to trace the number of rows processed by COPY. However in 9.3, this limitation is no more exists with procedural language. There are many languages supported in PostgreSQL, However I chose to test this with mostly used language plperl and plpython. Below are some snapshot pre-9.3 and in 9.3.
<br />
Lets check with plperl. Following is a plperl function which can be use:
<br />
<br />
<b>Definition of table is given below:</b>
<br />
<pre class="cpp" name="code"> Table "public.test_copy"
Column | Type | Modifiers
--------+---------+-----------
id | numeric |
</pre>
<b><br /></b>
<b>Content of data file:</b>
<br />
<pre class="cpp" name="code">cat /tmp/test.data
1
2
3
4
5
</pre>
<br />
Following is a plperl function which can be use for testing in pre-9.3 and in 9.3
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION test_copy() RETURNS integer
AS $$
my $rv = spi_exec_query("COPY test_copy FROM '/tmp/test.data'");
my $status = $rv->{status};
my $nrows = $rv->{processed};
return $nrows;
$$ LANGUAGE plperl;
</pre>
<br />
If we execute COPY command on psql prompt, user will get message like given below:
<br />
<pre class="cpp" name="code">worktest=# COPY test_copy from '/tmp/test.data';
COPY 5
</pre>
which shows COPY has processed 5 rows and accordingly loaded in table.
<br />
If we use above plperl function in <b>pre-9.3</b>, user will get following result:
<br />
<pre class="cpp" name="code">worktest=# select split_part(version(),' ',2) as version;
version
----------
9.2.4.10
(1 row)
worktest=# select test_copy();
test_copy
-----------
0
(1 row)
</pre>
<br />
which shows function was not able to get the number of rows processed by COPY command.
However if we use same plperl function <b>in 9.3</b>, we will get following result:
<br />
<pre class="cpp" name="code">postgres=# select split_part(version(),' ',2) as version;
version
----------
9.3beta2
(1 row)
postgres=# select test_copy();
test_copy
-----------
5
(1 row)
</pre>
which shows that plperl function in 9.3 is able to get the number of rows processed.
<br />
<br />
Similarly we can use following plpython function to test this new feature:
<br />
<pre class="cpp" name="code">CREATE FUNCTION result_copy_test(cmd text) RETURNS int
AS $$
plan = plpy.prepare(cmd)
plpy.info(plan.status())
result = plpy.execute(plan)
return result.nrows()
$$ LANGUAGE plpythonu;
</pre>
<br />
<b> pre-9.3 </b>
<br />
<pre class="cpp" name="code"></pre>
<pre class="cpp" name="code">worktest=# SELECT result_copy_test($$ COPY test_copy FROM '/tmp/test.data' $$);
INFO: True
CONTEXT: PL/Python function "result_copy_test"
result_copy_test
------------------
0
(1 row)
</pre>
which shows function was not able to get the processed rows by COPY command.
<br />
<b><br /></b>
<b> In 9.3 </b>
<br />
<pre class="cpp" name="code"></pre>
<pre class="cpp" name="code">postgres=# SELECT result_copy_test($$ COPY test_copy FROM '/tmp/test.data' $$);
INFO: True
CONTEXT: PL/Python function "result_copy_test"
result_copy_test
------------------
5
(1 row)
</pre>
<br />
which shows function was able to get the processed rows of COPY command.
<br />
<br />
<b> 2. Allow GET DIAGNOSTICS x = ROW_COUNT to access rows processed by COPY </b><br />
<br />
This is an enhancement in plpgsql, if user wants to access the rows processed by COPY command in plpgsql Block, then he can use
GET DIAGNOSTICS variable = ROW_COUNT, which was not possible before 9.3.<br />
To check with plpgsql, user can use either create a sample function or can use following anonymous block to verify this feature. I will be using DO block of plpgsql to verify this feature.
Example is given below:<br />
<b><br /></b>
<b>PL/pgSQL anonymous block </b>
<br />
<pre class="cpp" name="code">DO $$
DECLARE
r int;
BEGIN
COPY test_copy FROM '/tmp/test.data';
GET DIAGNOSTICS r = row_count;
RAISE NOTICE 'processed rows => %',r;
END;
$$ language plpgsql;
</pre>
<br />
<b> pre-9.3 </b>
<br />
<pre class="cpp" name="code">worktest=# DO $$
worktest$# DECLARE
worktest$# r int;
worktest$# BEGIN
worktest$# COPY test_copy FROM '/tmp/test.data';
worktest$# GET DIAGNOSTICS r = row_count;
worktest$# RAISE NOTICE 'processed rows => %',r;
worktest$# END;
worktest$# $$ language plpgsql;
NOTICE: processed rows => 0
DO
</pre>
which shows pre-9.3 was not able to get processed rows.
<br />
<b><br /></b>
<b> In 9.3 </b>
<br />
<pre class="cpp" name="code">postgres=# DO $$
postgres$# DECLARE
postgres$# r int;
postgres$# BEGIN
postgres$# COPY test_copy FROM '/tmp/test.data';
postgres$# GET DIAGNOSTICS r = row_count;
postgres$# RAISE NOTICE 'processed rows => %',r;
postgres$# END;
postgres$# $$ language plpgsql;
NOTICE: processed rows => 5
DO
postgres=
</pre>
<br />
In 9.3, plpgsql is able to get processed rows. Interesting.
<br />
<b><br /></b>
<b> 3. Allow use of RETURN with a composite expression in PL/pgSQL.</b><br />
<br />
This is new addition in PL/pgSQL and also useful for user who is reluctant to declare variable too much and wants RETURN in PL/pgSQL to return the expression.
<br />
This feature was not available pre-9.3 PL/pgSQL. However, in 9.3, user can use this easily. Below is an example.
<br />
<pre class="cpp" name="code">create type footype as (x int, y varchar);
create or replace function foo() returns footype as $$
begin
return (1, 'hello')::footype;
end;
$$ language plpgsql;
</pre>
<br />
<b>Lets try with pre-9.3 first </b>,
when user will try to create above function user will get following error message in pre-9.3:
<br />
<pre class="cpp" name="code">worktest=# create or replace function foo() returns footype as $$
worktest$# begin
worktest$# return (1, 'hello')::footype;
worktest$# end;
worktest$# $$ language plpgsql;
ERROR: RETURN must specify a record or row variable in function returning row
LINE 3: return (1, 'hello')::footype;
</pre>
<br />
<b>In 9.3</b>, due to addition of above feature, this works great.
<br />
<pre class="cpp" name="code">postgres=# create or replace function foo() returns footype as $$
postgres$# begin
postgres$# return (1, 'hello')::footype;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=#
</pre>
<br />
And user can see the working output too:
<br />
<pre class="cpp" name="code">postgres=# select foo();
foo
-----------
(1,hello)
(1 row)
</pre>
<br />
<br />
<b> 4. New in PL/Python </b><br />
<br />
There are some good improvement coming in plpython too. These improvement will be useful for users who like to make plpython and wants to debug the function or wants to print some useful information. This was missing in pre-9.3. Lets look at whats new in plptyhon.
<br />
<b><br /></b>
<b> i. Addition of object string in Pl/Python </b><br />
<br />
Before 9.3, whenever user wants to look at the information provided by object handler in Pl/Python, it doesn't used to give useful information. However, In 9.3, this is going to give some useful information which will be helpful for Pl/Python users.
<br />
<br />
Lets look at example, how this change is useful. Following is small code which can be use for testing this new addition:
<br />
<pre class="cpp" name="code">CREATE FUNCTION test_debug_info() RETURNS text AS $$
try:
rv=plpy.execute("SELECT datname FROM pg_catalog.pg_database",5)
plpy.info(rv);
except plpy.SPIError:
return "Not working"
else:
return "Working good"
$$ LANGUAGE plpythonu;
</pre>
<br />
<b> pre 9.3 </b>
<br />
<pre class="cpp" name="code">worktest=# select test_debug_info() ;
INFO: <plyresult 0x7f3594b8f270="" at="" object="">
CONTEXT: PL/Python function "test_debug_info"
test_debug_info
-----------------
Working good
(1 row)
</plyresult></pre>
<br />
Above user can see INFO hasn't given the very much useful information.
<br />
<b> In 9.3 </b>
<br />
<pre class="cpp" name="code">postgres=# select test_debug_info() ;
INFO: <plyresult datname="" nrows="3" postgres="" rows="[{" status="5" template0="" template1="">
CONTEXT: PL/Python function "test_debug_info"
test_debug_info
-----------------
Working good
(1 row)
</plyresult></pre>
<br />
Above you can see INFO has given some useful information about status, number rows and rows return by the query. Very useful, if somebody wants to know what handler contains in Pl/Python.
<br />
<b><br /></b>
<b> ii. Conversion of OID values to proper Pl/Python numeric type </b><br />
<br />
Before 9.3, Database type OID used to be treated as string in Pl/Python, which makes developers to convert string into number data type in plptyhon and do some processing on it, which is kind of extra coding. However in 9.3 they don't have to worry any more.
Below is an example to test this feature.
<br />
<pre class="cpp" name="code">CREATE FUNCTION test_type_conversion_oid(x oid) RETURNS oid AS $$
plpy.info(x, type(x))
return x +1
$$ LANGUAGE plpythonu;
</pre>
<b><br /></b>
<b>Lets look at the pre 9.3</b>
<br />
<pre class="cpp" name="code">worktest=# select test_type_conversion_oid(123456);
INFO: ('123456', <type str="">)
CONTEXT: PL/Python function "test_type_conversion_oid"
ERROR: TypeError: cannot concatenate 'str' and 'int' objects
CONTEXT: Traceback (most recent call last):
PL/Python function "test_type_conversion_oid", line 3, in <module>
return x +1
PL/Python function "test_type_conversion_oid"
</module></type></pre>
<br />
which shows direct number operations is not possible with OID in pre9.3, since OID in pre 9.3 is treated as string.
<br />
<b><br /></b>
<b> In 9.3 </b>
<br />
<pre class="cpp" name="code">postgres=# select test_type_conversion_oid(123456)
postgres-# ;
INFO: (123456L, <type long="">)
CONTEXT: PL/Python function "test_type_conversion_oid"
test_type_conversion_oid
--------------------------
123457
(1 row)
</type></pre>
This works great and direct number operation with OID is possible. A good addition in plpython.
<br />
<b><br /></b>
<b> iii. Handle SPI errors raised explicitly (with PL/Python's RAISE) the same as internal SPI errors </b><br />
<br />
This is new addition to plpython. Now in plpython function's body, user can raise SPIError/exceptions using "raise" statement of plpython, which was missing in pre 9.3. Now in 9.3, user will be able to use "raise" statement to raise exceptions. Addition to this, if user sets the sqlstate attribute, plpython preserver that change.
Below is an example:
<br />
Following is definition of function which can be use for testing this addition in pre 9.3 and in 9.3
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION plpy_raise_spiexception() RETURNS void AS $$
raise plpy.spiexceptions.DivisionByZero()
$$ LANGUAGE plpythonu;
</pre>
<br />
Lets use anonymous block of plpgsql to test how raise exception can be handle.<br />
<b><br /></b>
<b> Before 9.3 </b>
<br />
<pre class="cpp" name="code">DO $$
BEGIN
SELECT plpy_raise_spiexception();
EXCEPTION WHEN division_by_zero THEN
RAISE NOTICE 'Found Exception';
END
$$ LANGUAGE plpgsql;
ERROR: spiexceptions.DivisionByZero:
CONTEXT: Traceback (most recent call last):
PL/Python function "plpy_raise_spiexception", line 2, in <module>
raise plpy.spiexceptions.DivisionByZero()
PL/Python function "plpy_raise_spiexception"
SQL statement "SELECT plpy_raise_spiexception()"
PL/pgSQL function inline_code_block line 3 at SQL statement
</module></pre>
<br />
As you can see Before 9.3, user will raise statement was not working properly for raising exception in plpython.
<br />
<b><br /></b>
<b> In 9.3 </b>
<br />
<pre class="cpp" name="code">postgres=# DO $$
postgres$# BEGIN
postgres$# SELECT plpy_raise_spiexception();
postgres$# EXCEPTION WHEN division_by_zero THEN
postgres$# RAISE NOTICE 'Found Exception';
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE: Found Exception
DO
</pre>
<br />
As you can see in 9.3. it works great!
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-9794408487830307962013-07-28T12:08:00.000-07:002013-07-28T12:16:42.227-07:00New in PostgreSQL 9.3: Client Application improvementsLong long time. I haven't blogged at all. So, I have decided whenever I get chance I will blog about the cool things going in Database Technology market. I have lot of topics to share. Some got lost in air. Some I retained in mind. Before I lost more. I decided to continue from PostgreSQL 9.3 features and accordingly will go towards about new things as it starts to popup.
PostgreSQL users must have heard about release of PostgreSQL 9.3 and they must have started testing PostgreSQL 9.3.<br />
<br />
In today series, I am going to blog about client application improvements done in PostgreSQL 9.3.<br />
<br />
Lets take it one by one.<br />
<br />
<b>1. New binary/command pg_isready. </b><br />
<br />
PostgreSQL 9.3 is coming with new binary called pg_isready. this command helps user to know the status of server/PostgreSQL cluster.<br />
<br />
Before 9.3, user used to have their own tool/script to check the status of PostgreSQL, for that they used execute some random SQL like "SELECT 1" and if the status is successful, PostgreSQL is running or if status is unsuccessful, then PostgreSQL 9.3.<br />
However, pg_isready utility/command is much better than old methods in the sense it covers following scenario for checking the status of PostgreSQL 9.3:<br />
<pre class="cpp" name="code"> a. Check if PostgreSQL is ready and accepting connections.
b. Check if PostgreSQL is ready and connection is acception/rejecting.
c. Check if PostgreSQL is non-responsive.
d. it provide exit code and also has option being quiet about message, which be useful for users using exit code which create their own health check and messaging in application.
</pre>
<br />
Different exit codes and Message use by pg_isready to tell the status of PostgreSQL is given below:
<br />
<pre class="cpp" name="code">1. exit code 0,
Message: "host:port - accepting connections"
2. exit code: 1
Message: "host:port - rejecting connections"
3. exit code: 2
Message: "host:port - no response"
</pre>
<br />
usage example is given below:[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_isready -h localhost<br />
<pre class="cpp" name="code">localhost:5445 - accepting connections </pre>
<b>2. --table (multiple table option) in pg_restore, clusterdb, reindexdb and vacuumdb </b>
<br />
--table (multiple table option) was missing in pre-9.3 PostgreSQL. I personally missed an option for specifying multiple tables in single command.
Now user can use multiple table option with following commands in PostgreSQL 9.3 onwards. Example is given below:<br />
<b><br /></b>
<b>Till 9.2, when user uses command like</b><br />
<pre class="cpp" name="code">pg_restore -t emp -t dept test.dmp
</pre>
user will get information of table which is last table mention in command, as given below:
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# pg_restore --version
pg_restore (EnterpriseDB) 9.2.4.10
[root@ip-10-159-51-181 ~]# pg_restore -Fc -t emp -t dept test.dmp
--
-- EnterpriseDB database dump
--
SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog, sys;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: dept; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace:
--
CREATE TABLE dept (
deptno numeric(2,0) NOT NULL,
dname character varying(14),
loc character varying(13)
);
ALTER TABLE public.dept OWNER TO enterprisedb;
--
-- EnterpriseDB database dump complete
--
</pre>
You can see in pre-9.3, pg_restore has shown the result of last table in list in pg_restore command.<br />
<b><br /></b>
<b>Now in 9.3, </b> user can mention multiple table names and will be able to listed all tables in command:
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_restore -Fc -t emp -t dept test.dmp
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: dept; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace:
--
CREATE TABLE dept (
deptno numeric(2,0) NOT NULL,
dname character varying(14),
loc character varying(13)
);
ALTER TABLE public.dept OWNER TO enterprisedb;
--
-- Name: emp; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace:
--
CREATE TABLE emp (
empno numeric(4,0) NOT NULL,
ename character varying(10),
job character varying(9),
mgr numeric(4,0),
hiredate timestamp without time zone,
sal numeric(7,2),
comm numeric(7,2),
deptno numeric(2,0),
CONSTRAINT emp_sal_ck CHECK ((sal > (0)::numeric))
);
ALTER TABLE public.emp OWNER TO enterprisedb;
--
-- PostgreSQL database dump complete
--
</pre>
similarly this option is enable for reindexdb. Example is given below:<br />
<br />
<b>Before 9.3:
</b><br />
<b><br /></b>
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# reindexdb --version
reindexdb (EnterpriseDB) 9.2.4.10
[root@ip-10-159-51-181 ~]# reindexdb -t emp -t dept -e
REINDEX TABLE dept;
</pre>
<br />
<b>In 9.3:
</b><br />
<b><br /></b>
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/reindexdb -t emp -t dept -e
REINDEX TABLE emp;
REINDEX TABLE dept;
</pre>
<br />
User can observer usage of this switch for clusterdb and vacuumdb too.<br />
Addition of this, it has reduced lot of effort for defining script which can recursively call the reindexdb/vacuumdb/clusterdb for each tables.<br />
<br />
9.3 gives option to user for using these binaries more efficiently in their environment and tools.<br />
<b><br /></b>
<b>3. --dbname/-d option for binaries: pg_dumpall, pg_basebackup and pg_receivexlog. </b><br />
<br />
Before 9.3, --dbname and -d option was available in psql, clusterdb, reindexdb command etc.<br />
However this option wasn't available for command like pg_dumpall, pg_basebackup and pg_receivexlog, which made users to define connection string for this binaries differently from other binaries. For example:<br />
<b><br /></b>
<b>Before 9.3:</b><br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# pg_basebackup --version
pg_basebackup (PostgreSQL) 9.2.4.10
[root@ip-10-159-51-181 ~]# pg_basebackup --help|grep dbname
</pre>
<b><br /></b>
<b>In 9.3, user can see this option available:</b>
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_basebackup --help|grep -i dbname
-d, --dbname=CONNSTR connection string
</pre>
<b><br /></b>
<b>4. remove warning message for psql, when psql uses to connect to old server. </b><br />
<br />
Example is given below:<br />
<b>Before 9.3:</b><br />
<br />
User used to get warning message something like given below:
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# psql -p 5444
psql (9.2.4.10, server 9.1.9.17)
WARNING: psql version 9.2, server version 9.1.
Some psql features might not work.
Type "help" for help.
</pre>
<b>Now 9.3 onwards:</b>
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -p5444
psql (9.3beta2, server 9.1.9.17)
Type "help" for help.
worktest=#
</pre>
<b>Note::</b> Warning message is kept if user uses psql to connect to higher major version of postgresql.<br />
<b><br /></b>
<b>5. psql --single-transaction mode for STDIN: </b><br />
<br />
Before 9.3, --single-transaction mode doesn't work for STDIN.<br />
For example if you have a set of commands which you want to pass to psql through STDIN, then it doesn't used to work.<br />
To make it work, people used to put the commands in a file and then they used to use the --single-transaction mode.<br />
However 9.3 onwards, it won't require any more. Example is given below:<br />
<br />
<b>Before 9.3:</b>
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# psql --version
psql (EnterpriseDB) 9.2.4.10
[root@ip-10-159-51-181 ~]# echo "
CREATE TABLE (id numeric);
CREATE TABLE a;
CREATE DATABASE test;"|psql --single-transaction
ERROR: syntax error at or near "("
LINE 1: CREATE TABLE (id numeric);
^
ERROR: syntax error at or near ";"
LINE 1: CREATE TABLE a;
^
CREATE DATABASE
</pre>
Above example shows that we there was error on 1st and 2nd command, however psql executed third command successfully.
<br />
<pre class="cpp" name="code">worktest=# select datname from pg_database where datname='test';
datname
---------
test
(1 row)
</pre>
<b><br /></b>
<b>9.3 on wards:</b>
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# echo "
CREATE TABLE (id numeric);
CREATE TABLE a;
CREATE DATABASE test;"|/usr/pgsql-9.3/bin/psql --single-transaction
ERROR: syntax error at or near "("
LINE 1: CREATE TABLE (id numeric);
^
ERROR: syntax error at or near ";"
LINE 1: CREATE TABLE a;
^
ERROR: current transaction is aborted, commands ignored until end of transaction block
[root@ip-10-159-51-181 ~]#
</pre>
<br />
You can see transaction was aborted when user uses the STDIN with psql --single-transaction.<br />
<br />
<b>6. Other improvements in psql.</b><br />
<br />
In 9.3, psql has further improved for tab completion and patter searching. This has been done by tuning of functions cost settings.<br />
<br />
<b>7. New addition in Backslash commands:</b><br />
<br />
9.3 also coming with some new backslash commands. Those are going to help in lot ways for user to monitor and using psql in efficient way in there script/monitoring.<br />
<br />
Following are new in backslash commands:<br />
<br />
<b>i. \watch. </b><br />
Similar to Linux system watch command, psql is also coming with \watch command. Using this command user can execute current buffer query repeatedly.<br />
For example, if user wants to monitor/watch the sessions made to postgresql database, then he/she can use command something like given below in psql to do that:<br />
<br />
<b>a. Start a psql session as given below:</b><br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -p 5445 -U enterprisedb -d worktest
psql (9.3beta2, server 9.2.4.10)
Type "help" for help.
worktest=#
</pre>
<b><br /></b>
<b> b. Now execute a query which you want to use repeatedly as given below </b>
<br />
<pre class="cpp" name="code">worktest=# select datname, usename, application_name, query from pg_stat_activity ;
datname | usename | application_name | query
----------+--------------+------------------+-------------------------------------------------------
-------------------
worktest | enterprisedb | psql | select datname, usename, application_name, query from
pg_stat_activity ;
(1 row)
</pre>
<b><br /></b>
<b> c. Now use command "\watch [seconds]" </b>
<br />
<pre class="cpp" name="code">worktest=# \watch 1
Watch every 1s Sat Jul 27 04:15:21 2013
datname | usename | application_name | query
----------+--------------+------------------+--------------------------------------------------------------------------
worktest | enterprisedb | psql | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)
Watch every 1s Sat Jul 27 04:15:22 2013
datname | usename | application_name | query
----------+--------------+------------------+--------------------------------------------------------------------------
worktest | enterprisedb | psql | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)
</pre>
Or user combine above too in one command to use it something like given below:
<br />
<pre class="cpp" name="code">worktest=# select datname, usename, application_name, query from pg_stat_activity \watch 1
datname | usename | application_name | query
----------+--------------+------------------+-------------------------------------------------------
-------------------
worktest | enterprisedb | psql | select datname, usename, application_name, query from
pg_stat_activity ;
(1 row)
Watch every 1s Sat Jul 27 04:36:00 2013
datname | usename | application_name | query
----------+--------------+------------------+--------------------------------------------------------------------------
worktest | enterprisedb | psql | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)
</pre>
<br />
In above, I wanted to use query to monitor the pg_stat_statement every seconds.<br />
<br />
<b>\watch commands interrupted/stop in following condition: </b>
<br />
<pre class="cpp" name="code">if user has interrupted the running watch.
if query fails.
</pre>
<b><br /></b>
<b> ii. \gset command to store query results in psql variables.</b><br />
<br />
New command is added in backslash. This is very interesting command and user can use it in multiple purpose. This backslash command allows users to set value of a variable based on single row/output of SQL.<br />
If SQL returns multiple output, then \gset will send proper message and will not set variable.<br />
<br />
There are multiple example of using this backslash, for example you can store the output of now query in a variable and can use in psql session as given below:<br />
<pre class="cpp" name="code">worktest=# select now() as timestamp
worktest-# \gset
worktest=# \echo :timestamp
27-JUL-13 04:53:13.214203 -04:00
worktest=#
</pre>
<br />
Some, user like me, can also use this command to prepare dynamic commands and can execute in session.<br />
For example I have users like test which I want to drop from PG instance, so, I can do something like given below:
<br />
<pre class="cpp" name="code">worktest=# SELECT replace(array_to_string(ARRAY(SELECT 'DROP USER '||usename||';' FROM pg_user where usename ~ 'test'),', '),',','') as drop_test_user
worktest-# \gset
worktest=# \echo "Verify the Drop test user command"
"Verify the Drop test user command"
worktest=# \echo :drop_test_user
DROP USER test; DROP USER test_su;
worktest=# :drop_test_user
DROP ROLE
DROP ROLE
</pre>
<b><br /></b>
<b> iii. improvement in \conninfo to show ssl information </b><br />
<br />
Before 9.3, backslash command conninfo used to show information about user,database, port and host. Even if user is using ssl connection. However psql in 9.3 will give ssl information too. Example is given below:<br />
<b>Before 9.3</b>
<br />
<pre class="cpp" name="code">edb=> \conninfo
You are connected to database "edb" as user "vibhor" on host "localhost" at port "5444".
</pre>
<b>In 9.3:</b>
<br />
<pre class="cpp" name="code">edb=> \conninfo
You are connected to database "edb" as user "vibhor" on host "localhost" at port "5444".
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
</pre>
<b><br /></b>
<b> iv. database name patter support in \l </b><br />
<br />
Backslash \l command people use to list the database in psql.<br />
However, before 9.3, this command didn't have the pattern search support.<br />
Now, in 9.3, this command has been improved in terms of displaying/listing the database based on pattern provided by user. Snapshot is given below:<br />
<b><br /></b>
<b>Before 9.3:</b>
<br />
<pre class="cpp" name="code">edb=> \l pe*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------------+----------+-------------+-------------+-------------------------------
edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
pem | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | enterprisedb=CTc/enterprisedb+
| | | | | pem_user=Tc/enterprisedb +
| | | | | pem_agent=Tc/enterprisedb
postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/enterprisedb +
| | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/enterprisedb +
| | | | | enterprisedb=CTc/enterprisedb
(5 rows)
\l: extra argument "pe*" ignored
</pre>
As you can see it has ignored the pattern based listing and listed all database.<br />
<b><br /></b>
<b>Now in 9.3:</b>
<br />
<pre class="cpp" name="code">edb=> \l pe*
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------+--------------+----------+-------------+-------------+-------------------------------
pem | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | enterprisedb=CTc/enterprisedb+
| | | | | pem_user=Tc/enterprisedb +
| | | | | pem_agent=Tc/enterprisedb
(1 row)
</pre>
Thats really a good enhancement in backslash command.<br />
<b>iv. Fix in "\g filename", which was affecting subsequent commands after an error. </b><br />
<br />
Before 9.3, if "\g filename" commands fails, then it was also affecting the execution of query after this backslash command.<br />
For example, if user executes query something like given below, then subsequent query which output user doesn't want to store in a file, will go in the filename mentioned in first command:
<br />
<pre class="cpp" name="code">worktest=# select 'a'/1 \g /tmp/test
ERROR: invalid input syntax for integer: "a"
LINE 1: select 'a'/1
^
worktest=# select 1/2;
worktest=#
</pre>
As you can see 1st SQL failed. However second SQL output not displayed on screen and it went in /tmp/test file. which is inconsistent.<br />
<br />
Now in 9.3 this has been fixed:
<br />
<pre class="cpp" name="code">worktest=# select 'a'/1 \g /tmp/test
ERROR: invalid input syntax for integer: "a"
LINE 1: select 'a'/1
^
worktest=# select 1/2;
?column?
------------------------
0.50000000000000000000
(1 row)
</pre>
<b><br /></b>
<b> v. Improvement in \df+ command to show Security label. </b><br />
If user creates following function with security definer
<br />
<pre class="cpp" name="code">CREATE FUNCTION test_func()
RETURNS integer
SECURITY DEFINER
AS
$$ SELECT 1;
$$ language sql;
</pre>
<b><br /></b>
<b>before 9.3,</b> \df+ command never used to give information on SECURITY label. Like in above case, SECURITY DEFINER. Below is output<br />
<br />
<b>before 9.3:</b>
<br />
<pre class="cpp" name="code">-[ RECORD 1 ]-------+-------------
Schema | oma
Name | test_func
Result data type | integer
Argument data types |
Type | normal
Volatility | volatile
Owner | enterprisedb
Language | sql
Source code | SELECT 1;
|
Description |
</pre>
<b>In 9.3:</b>
<br />
<pre class="cpp" name="code">Schema | oma
Name | test_func
Result data type | integer
Argument data types |
Type | normal
Security | definer
Volatility | volatile
Owner | enterprisedb
Language | sql
Source code | SELECT 1;
|
Description |
</pre>
<br />
In above you can see column name Security.<br />
<b><br /></b>
<b>8. New improvement psql Output: </b><br />
<br />
9.3 is also coming with some new improvements in output psql commands below are details of those:<br />
<br />
<b>i. latex-longtable support and border=3 style for latex output. </b><br />
<br />
Those who are not familiar with LaTex, I would recommend following two links:
<br />
<pre class="cpp" name="code"> http://www.latex-project.org
http://en.wikipedia.org/wiki/LaTeX
</pre>
So,those, who are familiar with Latex, for them this new feature will be helpful.<br />
latex-longtable allows tabular format output to in multiple pages. With this there border=3 defined for latex output in psql 9.3<br />
<br />
<b>ii. --tuples-only (-t) with --expanded (-x) not to show "(No rows)" </b><br />
<br />
Before 9.3, if someone uses --tuples-only with --expanded switch in psql and query executed doesn't return any record,then combination two switches used to print "(No rows)". As given below:
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# psql -t -x -c "select datname from pg_database where datname ~ 'temporar'"
(No rows)
</pre>
which used to give some inconvenience in scripting psql.<br />
However now, 9.3 onwards, if query doesn't return any record/row, then psql will return nothing. As given below:
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -t -x -c "select datname from pg_database where datname ~ 'temporar'"
[root@ip-10-159-51-181 ~]#
</pre>
which helps in better scripting.<br />
<br />
<b>ii. --no-align (-A) with --expanded (-x) not to print empty lines. </b><br />
<br />
Before 9.3, combination of --no-align (-A) with --expanded (-x) used to print empty line, if query doesn't return any value. However this is fixed. In 9.3 it will return nothing, as shown below:<br />
<b><br /></b>
<b>Before 9.3</b>
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# psql -t -c "select datname from pg_database where datname ~ 'temporar'" -x -A
[root@ip-10-159-51-181 ~]#
</pre>
<b><br /></b>
<b> In 9.3:</b>
<br />
<pre class="cpp" name="code">[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -t -c "select datname from pg_database where datname ~ 'temporar'" -x -A
[root@ip-10-159-51-181 ~]#
</pre>
From scripting perspective, this is good improvement.<br />
<br />
<b><br /></b>
<b>9. Features/imporvement in pg_dump</b>
<b> i. parallel dump option using --jobs in pg_dump </b><br />
<br />
9.3 has added new performance improvement in pg_dump.<br />
<br />
pg_dump can be faster in unloading data from PG database. It has been improved to do with parallel jobs.<br />
<br />
There are some notes, which user should know:
<br />
<pre class="cpp" name="code"> 1. parallel dump is supported if user uses dump format directory. i.e -Fd.
2. parallel dump can increase load on server, so user has to be cautious about choosing number of jobs for pg_dump.
3. --jobs will open a n+1 connections to database, so, user would like to set the max_connections appropriately. Out of n+1 connections, one connection will be used by master process of pg_dump and n connections will be used by master workers.
4. Worker process in pg_dump takes SHARED LOCK with NOWAIT option. so, if there is any exclusive lock on table then worker process will not be able to acquire SHARED LOCK will exit and accordingly inform to master process,which will abort the pg_dump.
5. for consistent backup, database needs to support synchronized snapshots which was introduced in 9.2. Therefore if user is planning to use pg_dump of 9.3 to take backup of pre-9.2 postgresql, then they have to make sure that database content doesn't change.
</pre>
Example of parallel dump is given below:
<br />
<pre class="cpp" name="code">pg_dump -U username -j4 -Fd -f <dump directory=""> databasename
</dump></pre>
<b><br /></b>
<b> ii. --dbname switch in pg_dump.</b><br />
<br />
Before 9.3, pg_dump didn't have the --dbname option. Since it was assummed that user will use database name at the end of pg_dump, as given below:
<br />
<pre class="cpp" name="code">
pg_dump -U username -p port -h host [database name]
</pre>
<br />
To keep the consistency of connection option with other utilities/binaries, in 9.3 --dbname option has been introduced. Now, user can also execute command like given below:
<br />
<pre class="cpp" name="code"> pg_dump -U username -p port -h host -d [database name]
</pre>
<b>10. New in initdb</b><br />
<br />
9.3 has also added new functionality and feature in initdb command too.<br />
<br />
<b>i. fsync the newly created data directory. </b><br />
<br />
In 9.3, initdb now make sure data directly is safely written to disk. which makes data directory created by initdb is more durable. There will be slight performance impact of using default fysnc. However user can disable this option by choosing --nosync (-N) option, which is not recommended.<br />
<br />
Below is some stats:
<br />
<pre class="cpp" name="code">With default:
real 0m4.304s
user 0m2.736s
sys 0m1.604s
with --nosync
real 0m4.129s
user 0m2.667s
sys 0m1.493s
</pre>
you can see there is not much difference as per performance.<br />
<b><br /></b>
<b> ii. --sync-only option in initdb. </b><br />
<br />
9.3 has also added new switch called sync-only. Using this option user can make sure existing data directory to be written safely to disk. Example of usage is given below:<br />
<pre class="cpp" name="code">-bash-4.1$ /usr/pgsql-9.3/bin/initdb --sync-only -D /var/lib/pgsql/test
syncing data to disk ... ok
-bash-4.1$
</pre>
<b><br /></b>
<b> iii. warning message if initdb is used to place data directory in top of file filesystem mount point.</b><br />
<br />
<b></b>In 9.3, initdb has been made more sensible on warning user,if user is trying to create data directory top of filesystem mount point. Following are some warning which user will get:
<br />
<pre class="cpp" name="code">It contains a dot-prefixed/invisible file, perhaps due to it being a mount point.
It contains a lost+found directory, perhaps due to it being a mount point.
Using a mount point directly as the data directory is not recommended. Create a subdirectory under the mount point
</pre>
<br />
<br />
Enjoy Learning more about 9.3!!!Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-72054528907859130712013-02-18T14:33:00.000-08:002013-07-28T11:27:39.389-07:00pg_xlog_location_diff function for PostgreSQL/PPASIn PostgreSQL 9.2, community has added a function pg_xlog_location_diff(), which is very useful for finding the difference between two xlog location in bytes and also useful for monitoring replication.
<br />
Detail of this function is given in following link:
<br />
<pre class="cpp" name="code">http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP
</pre>
However this function is not available for users of PostgreSQL/PPAS 9.0/9.1 users. So, I thought to write same function plpgsql so, that users can take benefit of same in 9.0/9.1.
<br />
Before using formula and developing function, lets understand what is xlog and offset.
Let's consider user has used function pg_current_xlog_location() function and he gets following information:
<br />
<pre class="cpp" name="code">worktest=# select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
1/D1012B80
(1 row)
</pre>
<br />
In above, first field before forward slash is the hexadecimal value of logical xlog file and second field i.e. D1012B80 is hexadecimal offset inside the logical xlogfile.
<br />
So, whenerver user sees information for xlog location, he gets xlog information in following format:
<br />
<pre class="cpp" name="code">(hexadecimal) xlog/ (hexadecimal) offset
</pre>
<br />
To calculate the difference between two xlog location, user can use following formula, which gives difference in hexadecimal:
<br />
<pre class="cpp" name="code">(FF000000 * xlog + offset) - (FF000000 * xlog + offset)
</pre>
<br />
where FF000000 is max value of offset i.e In PostgreSQL/PPAS offset value can go from 00000000 to FF000000 and if we convert that in decimal, it gives maximum 4278190080 bytes (4080MB)
<br />
Based on above formula, following is plpgsql function which can be use to get the difference:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text)
RETURNS numeric
LANGUAGE plpgsql
AS
$function$
DECLARE
offset1 text;
offset2 text;
xlog1 text;
xlog2 text;
SQL text;
diff text;
BEGIN
/* Extract the Offset and xlog from input in
offset and xlog variables */
offset1=split_part($1,'/',2);
xlog1=split_part($1,'/',1);
offset2=split_part($2,'/',2);
xlog2=split_part($2,'/',1);
/* Prepare SQL query for calculation based on following formula
(FF000000 * xlog + offset) - (FF000000 * xlog + offset)
which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome
so convert into decimal and then calculate the difference */
SQL='SELECT (x'''||'FF000000'||'''::bigint * x'''||xlog1||'''::bigint
+ x'''||offset1||'''::bigint)'||'
-
(x'''||'FF000000'||'''::bigint * x'''||xlog2||'''::bigint
+ x'''||offset2||'''::bigint)';
EXECUTE SQL into diff;
/* Return the value in numeric by explicit casting */
RETURN diff::numeric;
END;
$function$;
</pre>
<br />
Usage example is given below:
<br />
<pre class="cpp" name="code">worktest=# select pg_xlog_location_diff_sql(pg_current_xlog_location(),'1/D009F578');
pg_xlog_location_diff_sql
---------------------------
16230472
(1 row)
</pre>
<br />
I hope this will help PostgreSQL/PPAS 9.0/9.1 users.Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-90014463371807987592012-10-07T15:17:00.000-07:002012-12-31T12:22:40.656-08:00New in Postgres Plus Advanced Server 9.2 Good News Postgres Plus Advanced Server Beta Version is now availabale, which has all the new feature of PostgreSQL 9.2, and it also has new features which are specific to Advanced Server.
<br />
I will cover/explain New features of PostgreSQL 9.2 later as per release Notes. However, the following link covers major features of PostgreSQL 9.2 and can be taken as reference.
<br />
<pre class="cpp" name="code">http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2
</pre>
In this Blog, I am going to cover only specific features of Advanced Server 9.2 Core. Which are:
<br />
<b><br /></b>
<b>1. INSERT APPEN HINT in PPAS 9.2</b>
<br />
PPAS 9.2, now supports INSERT append hint. This is a very interesting feature and it is very useful for users who frequently delete records in Bulk and do bulk of INSERTs.
This hint can provide some benefits in INSERTs. This hint makes PPAS not use Free Space Map and Append the rows at the end of relation(table).
Its usage is given below:
<br />
<pre class="cpp" name="code">INSERT /*+append*/ INTO tab21115 VALUES(1,'abc',sysdate);
</pre>
<b>2. Procedure Called like Function Call.</b>
<br />
PPAS 9.2 now allows calling procedure with following syntax:
<br />
<pre class="cpp" name="code">SELECT * FROM procedure(arg1,arg2,…);
</pre>
This type of Procedure call is allowed for Procedures which have OUT/INOUT Params. With this, user can use exec Function.
<br />
<br />
lets see how it works:
<br />
<b><br /></b>
<b>a. Create a Procedure as Given below:</b>
<br />
<pre class="cpp" name="code">CREATE OR REPLACE PROCEDURE foo_proc(A IN INT, B INOUT INT, C OUT INT)
AS
BEGIN
b:=a+b;
c:=b+1;
END;
</pre>
Till 9.1 and even in 9.2, user can do something like given below:
<br />
<pre class="cpp" name="code">DECLARE
d int:=1;
e int:=2;
f int;
BEGIN
foo_proc(d,e,f);
DBMS_OUTPUT.PUT_LINE('e = '||e);
DBMS_OUTPUT.PUT_LINE('d = '||d);
DBMS_OUTPUT.PUT_LINE('f= '||f);
END;
e = 3
d = 1
f= 4
</pre>
Now in 9.2, user can also do this:
<br />
<pre class="cpp" name="code">edb=# select * from foo_proc(1,2);
b | c
---+---
3 | 4
(1 row)
</pre>
In case the user has Procedure and Function with a same name and the user wants to execute procedure using select command, then SELECT command is going to give preference to Function over Procedure as given below:
<br />
Function Definition:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION foo(a INT) RETURN INT
AS
BEGIN
RETURN 2;
END;
CREATE PROCEDURE foo(A int, B OUT int)
AS
BEGIN
B:=a+1;
END;
edb=# select * from foo(4);
foo
-----
2
</pre>
However, user can still use EXEC command to execute Procedure as given below:
<br />
<pre class="cpp" name="code">edb=# exec foo(4);
?column?
----------
5
(1 row)
</pre>
<pre class="cpp" name="code"></pre>
Or if you are using Anonymous function then PERFORM will also work with Procedure as given below:
<br />
<pre class="cpp" name="code">edb=# DECLARE
edb-# a int:=4;
edb$# b int;
edb$# BEGIN
edb$# PERFORM foo(a,b);
edb$# DBMS_OUTPUT.PUT_LINE('b = '||b);
edb$# END;
b = 5
EDB-SPL Procedure successfully completed
</pre>
<b>3. Object Type Support:</b>
<br />
PPAS already has Object Type support. However, the new version is coming with a new enhancement in that support. In New Version, user would be able to create Objects with Attributes, Functions, and Procedures. This gives an advantage of reduced coding in terms of defining Object types and maintaining it.
<br />
<br />
Example is given below:
<br />
<br />
a. Define a Type:
<br />
<pre class="cpp" name="code"> CREATE OR REPLACE TYPE PersonObj AS OBJECT (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
date_of_birth DATE,
MEMBER FUNCTION getAge RETURN NUMBER
);
</pre>
b. Define a Type Body:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE TYPE BODY PersonObj AS
MEMBER FUNCTION getAge RETURN NUMBER AS
BEGIN
RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
END getAge;
END;
</pre>
c. Define a Table based on Body type:
<br />
<pre class="cpp" name="code">CREATE TABLE people (
id NUMBER(10) NOT NULL,
person PersonObj
);
</pre>
d. To insert Data, Default constructor can be use as given below:
<br />
<pre class="cpp" name="code">INSERT INTO people
VALUES (1, PersonObj('John','Doe',
TO_DATE('01/01/1999','DD/MM/YYYY')));
</pre>
e. With the following way, a user can access the data in the table:
<br />
<pre class="cpp" name="code">SELECT p.id,
(p.person).first_name,
p.person.getAge() age
FROM people p;
id | first_name | age
----+------------+-----
1 | John | 13
2 | Jane | 13
(2 rows)
</pre>
<b>4. PL/SQL Subtypes:</b>
<br />
Subtypes is considered as a TYPE, which is defined on base/user defined types with some constraints, syntax for declaring SUBTYPE in PL/SQL is given below:
<br />
<pre class="cpp" name="code">SUBTYPE <subtype_name> IS <type_name> [(constraint)] [NOT NULL];
</type_name></subtype_name></pre>
Example is given below:
<br />
Example 1:
<br />
<pre class="cpp" name="code">DECLARE
SUBTYPE INT2 IS NUMBER;
v2 INT2 (3,0);
BEGIN
v2:= 123;
DBMS_OUTPUT.PUT_LINE ('V2 = '|| v2);
END;
</pre>
Example 2:
<br />
<pre class="cpp" name="code">DECLARE
SUBTYPE v_word IS VARCHAR2(10) NOT NULL;
verb v_word := 'verb';
noun v_word := 'noun';
BEGIN
noun :='n1';
DBMS_OUTPUT.PUT_LINE (UPPER(verb));
DBMS_OUTPUT.PUT_LINE (UPPER(noun));
END;
</pre>
<b>5. DROP TABLE CASCADE CONSTRAINTS in 9.2:</b>
<br />
PPAS 9.2 now supports DROP TABLE CASCADE CONSTRAINTS, this is a very intersting feature and is not available in PostgreSQL 9.2.
<br />
<br />
Using this command, user would be able to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause and such referential integrity constraints exist, then the database returns an error and does not drop the table.
<br />
<br />
This is useful, when somebody wants to drop Master table but doesn't want to drop child table, which has Foreign Key Constraint and is referencing the Master.<br />
<br />
An example is given below:
<br />
<pre class="cpp" name="code"></pre>
<pre class="cpp" name="code">CREATE TABLE master(id numeric primary key);
CREATE table child(id numeric references master(id));
insert into master values(1);
insert into child values(1);
edb=# drop table master cascade constraints;
NOTICE: drop cascades to constraint child_id_fkey on table child
DROP TABLE
edb=# \d child
Table "enterprisedb.child"
Column | Type | Modifiers
--------+---------+-----------
id | numeric |
</pre>
As you can see above, I have dropped the Master table, on which child table has referential integrity constaint. However, my Child table still exists. PostgreSQL 9.2, doesn't have CASCADE CONSTRAINTS. Therefore, user has to first drop the constraints on table which has referential integrity constraints and then drop the referenced table. This process requires user to find all tables which has referential integrity constraints that refer to primary key of master table. This commands helps DBAs/Users not to worry about finding the child tables.<br />
<br />
<b>6. Allow throwing Multiple Errors as Warning, while compiling Procedures/Functions using SPL.</b>
<br />
This is also a very interesting enhancement found for SPL. Whenever the user wants to compile a function/procedure in SPL, it will show all the error messages in the particular Function/Procedure Body, so that the user doesn't have to re-visit their code everytime to fix the other issue. An example is given below:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION foo() RETURN integer
AS
BEGIN
a:=a=1
;
B:=b + 1;
I am making fool;
END;
Output:
ERROR: "a" is not a known variable
LINE 4: a:=a=1
^
ERROR: "b" is not a known variable
LINE 6: B:=b + 1;
^
ERROR: syntax error at or near "I"
LINE 7: I am making fool;
^
ERROR: compilation of SPL function/procedure "foo" failed due to 3 errors
</pre>
As you can see above, it has given all the mistakes that I made in Function Body, and now I can fix all of them in one go, to reduce the overhead of executing again and again.
<br />
This is very useful for Developers/DBAs.
<br />
<b><br /></b>
<b>7. DEFAULT for Types declared in Packages:</b>
<br />
Now, in PPAS 9.2, user would be able to define DEFAULT value for particular type, as given below:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE PACKAGE INITPKG_pro_b4_default AS
PROCEDURE show_default_values;
n1 NUMBER DEFAULT 20;
c1 VARCHAR2 DEFAULT 'Default';
END;
CREATE OR REPLACE PACKAGE BODY INITPKG_pro_b4_default AS
PROCEDURE show_default_values IS
n number;
BEGIN
dbms_output.put_line(c1);
dbms_output.put_line(n1);
n1 := n1*n1;
n1 := SQRT(n1);
dbms_output.put_line(n1);
END;
END;
edb=# exec INITPKG_pro_b4_default.show_default_values;
Default
20
20.000000000000000
EDB-SPL Procedure successfully completed
</pre>
<b><br /></b>
<b>8. TABLE Expression support for Nested Tables.</b>
<br />
Now, in 9.2, user would be able to use TABLE Expressions for Nested tables. This feature was missing and asked by many Oracle DBAs. Table Expressions allows the user to query a collection in the FROM Clause like a Table.
<br />
<pre class="cpp" name="code">edb=# CREATE OR REPLACE TYPE comp_typ_str_21189 IS TABLE OF VARCHAR2(100);
CREATE TYPE
edb=#
edb=# select * from table(comp_typ_str_21189('PPP','QQQ')) ;
column_value
--------------
PPP
QQQ
(2 rows)
</pre>
<b><br /></b>
<b>9. INPUT/OUTPUT Functions for NESTED TABLES:</b>
<br />
PPAS 9.2 supports user defined input/output functions for NESTED TABLE. This feature was missing in 9.1.
<br />
Some examples are given below:
<br />
<pre class="cpp" name="code">edb=# create or replace type mytab as table of varchar2(90);
CREATE TYPE
edb=# Create or replace function fun return mytab
edb-# as
edb$# begin
edb$# return mytab('a','b',3);
edb$# end;
CREATE FUNCTION
edb=# select fun;
fun21168
----------
{a,b,3}
(1 row)
edb=#
edb=# create or replace function fun return mytab
edb-# as
edb$# nvar mytab;
edb$# begin
edb$# nvar := mytab();
edb$# nvar.extend(4);
edb$# nvar(1) := 'foo';
edb$# nvar(2) := NULL;
edb$# nvar(3) := 'deleteme';
edb$# nvar(4) := 'bar';
edb$# return nvar;
edb$# end;
CREATE FUNCTION
edb=# --Verify User's is able to see that data
edb=# select fun;
fun21168
-------------------------
{foo,NULL,deleteme,bar}
(1 row)
</pre>
<b>9. LOG is no more a reserved keyword for functions.</b><br />
<br />
Till PPAS 9.1, LOG was a reserved keyword and users were not allowed to create functions using this Keyword. In 9.2, a user would be able to use this keyword.<br />
<pre class="cpp" name="code">edb=# CREATE OR REPLACE FUNCTION log( a int ) return int as
edb$# BEGIN
edb$# dbms_output.put_line('Function LOG is called');
edb$# return a*2;
edb$# END;
CREATE FUNCTION
edb=# SELECT LOG(10);
Function LOG is called
log
-----
20
(1 row)
</pre>
<b>10. Variables can be named as current_date.</b>
<br />
We know that current_date is special function which returns current date of PostgreSQL/PPAS DB. Till PPAS 9.1, users were not allowed to use this reserved name in variables. Now, in 9.2, users would be able to use it. As given below:
<br />
<pre class="cpp" name="code">edb=# create table t_currentdate(current_date int);
CREATE TABLE
edb=# desc t_currentdate
Table "enterprisedb.t_currentdate"
Column | Type | Modifiers
--------------+---------+-----------
current_date | integer |
create or replace procedure proc_currentdate(current_date date) is
begin
dbms_output.put_line(current_date);
end;
edb=# exec proc_currentdate(current_date);
05-OCT-12 00:00:00
EDB-SPL Procedure successfully completed
</pre>
<b>11. New Data Type STRING.</b>
<br />
Since Oracle supports string as data type and this datatype wasn't available in PPAS 9.1, we have included it in 9.2. String is an alias to VARCHAR Data type. Example is given below:
<br />
<pre class="cpp" name="code">edb=# CREATE TABLE test_string(col string)
edb-# ;
CREATE TABLE
edb=# insert into test_string values('As an Example');
INSERT 0 1
edb=# select * from test_string;
col
---------------
As an Example
(1 row)
edb=# \d test_string
Table "enterprisedb.test_string"
Column | Type | Modifiers
--------+-------------------+-----------
col | character varying |
</pre>
<b>12. NVARCHAR2 data type support in 9.2</b>
<br />
PPAS 9.2 allowes users to use NVARCHAR2 as a datatype. In PPAS 9.2, NVARCHAR2 is mapped to VARCHAR data type. This is only for an Oracle user, who always asked question on having this in PPAS.
<br />
Example is given below:
<br />
<pre class="cpp" name="code">edb=# CREATE TABLE test_nvarchar(col NVARCHAR2(10))
edb-# ;
CREATE TABLE
edb=# \d test_nvarchar
Table "enterprisedb.test_nvarchar"
Column | Type | Modifiers
--------+-----------------------+-----------
col | character varying(10) |
</pre>
<b>13. MultiByte and string delimiter support in EDB*loader.</b>
<br />
EDB*Loader in PPAS 9.2, is more efficient, compared to 9.1, in loading data. There were some cases, in which it has been observed that it is difficult to find single character delimiter for data in file as the data in a file has all possible characters. To load those kind of data files, users can now define there own STRING Delimiter. An example is given below:
<br />
<pre class="cpp" name="code">Data File: data.log:
1$$abc
2$$ccc
3$$dddddddd
Control FIle:
LOAD DATA
INFILE '/tmp/data.log'
BADFILE '/tmp/data.bad'
truncate INTO table edb_loader
FIELDS TERMINATED BY '$$' optionally enclosed by '"'
(id , col )
-bash-4.1$ edbldr userid=enterprisedb/ control=/tmp/edb.ctl log=/tmp/edb.log
EDB*Loader: Copyright (c) 2007-2012, EnterpriseDB Corporation.
Successfully processed (3) records
-bash-4.1$ psql
psql (9.2.0.1)
Type "help" for help.
edb=# select * from edb_loader ;
id | col
----+----------
1 | abc
2 | ccc
3 | dddddddd
(3 rows)
</pre>
Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-89397335088594449162012-07-29T11:43:00.001-07:002012-07-29T11:44:30.771-07:00List user privileges in PostgreSQL/PPAS 9.1PostgreSQL has some useful functions which can be use to know about the privilege of a user on a particular Database object. Those functions is available in following link:
<br />
<pre class="cpp" name="code">http://www.postgresql.org/docs/9.1/static/functions-info.html
</pre>
<br />
Functions has_*_privilege in PostgreSQL/PPAS is good to know about privilege a user has on one database objects and these function returns boolean value true or false.
<br />
<br />
Since, DBAs/Users are interested in listing objects and privileges of a Database User and currently PostgreSQL doesn't have a view, which DBA can use to list users privileges on objects for a particular database. Therefore, I thought about making some functions, which can be used to list users privileges, based on what is available in PostgreSQL/PPAS 9.1. These are basic functions and can be expanded, as per need, to show more privileges like WITH GRANT OPTION.
<br />
<br />
Following are functions which can use to get the privileges of a particular user:
<br />
<br />
1. Function for table privileges:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text[])
AS
$$
SELECT $1,c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='r' and
has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFRENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
</pre>
<br />
Example:
<br />
<pre class="cpp" name="code">edb=# select * from table_privs('test_user');
username | relname | privs
-----------+---------+------------------------
test_user | test_id | {SELECT,UPDATE,DELETE}
(1 row)
</pre>
<br />
2. Database privileges:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION database_privs(text) RETURNS table(username text,dbname name,privileges text[])
AS
$$
SELECT $1, datname, array(select privs from unnest(ARRAY[
( CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL) FROM pg_database c WHERE
has_database_privilege($1,c.oid,'CONNECT,CREATE,TEMPORARY,TEMP') AND datname <> 'template0';
$$ language sql;
</pre>
<br />
Example:
<br />
<pre class="cpp" name="code">edb=# select * from database_privs('test_user');
username | dbname | privileges
-----------+-----------+-----------------------------
test_user | template1 | {CONNECT}
test_user | edb | {CONNECT,TEMPORARY,CONNECT}
(2 rows)
</pre>
<br />
3. Tablespace privileges:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION tablespace_privs(text) RETURNS table(username text,spcname name,privileges text[])
AS
$$
SELECT $1, spcname, ARRAY[
(CASE WHEN has_tablespace_privilege($1,spcname,'CREATE') THEN 'CREATE' ELSE NULL END)] FROM pg_tablespace WHERE has_tablespace_privilege($1,spcname,'CREATE');
$$ language sql;
</pre>
Example:
<br />
<pre class="cpp" name="code">edb=# select * from tablespace_privs('test_user');
username | spcname | privileges
-----------+---------+------------
test_user | test | {CREATE}
(1 row)
</pre>
<br />
4. Foreign Dataa Wrapper privileges
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION fdw_wrapper_privs(text) RETURNS table(username text,fdwname name,privleges text[])
AS
$$
SELECT $1, fdwname, ARRAY[
(CASE WHEN has_foreign_data_wrapper_privilege($1,fdwname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_foreign_data_wrapper WHERE has_foreign_data_wrapper_privilege($1,fdwname,'USAGE');
$$ language sql;
</pre>
<br />
Example:
<br />
<pre class="cpp" name="code">edb=# select * from fdw_wrapper_privs('test_user');
username | fdwname | privleges
-----------+--------------+-----------
test_user | libpq_dblink | {USAGE}
(1 row)
</pre>
<br />
5. To find foreign server privileges following functions can be use:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION foreign_server_privs(text) RETURNS table(username text, srvname name, privileges text[])
AS
$$
SELECT $1, s.srvname , ARRAY[
(CASE WHEN has_server_privilege($1,srvname,'USAGE') THEN 'USAGE' ELSE NULL END)] from pg_catalog.pg_foreign_server s WHERE has_server_privilege ($1,srvname,'USAGE');
$$
language sql;
</pre>
<br />
6. To find language priveleges, following function can be use:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION language_privs(text) RETURNS table(username text,srvname name, privileges text[])
AS
$$
SELECT $1, l.lanname, ARRAY[(CASE WHEN has_language_privilege($1,lanname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_language l where has_language_privilege($1,lanname,'USAGE');
$$ language sql;
</pre>
<br />
7. To find schema privileges of a user following can be use:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privieleges text[])
AS
$$
SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)
FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE') AND c.nspparent=0;
$$ language sql;
</pre>
<b><br /></b><br />
<b>Note::</b> Above function can be use in Advanced Server. For schema_privilege in PostgreSQL, user can try following function:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privieleges text[])
AS
$$
SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)
FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE');
$$ language sql;
</pre>
<br />
8. To get privilege of a particular with view name, following function can be use:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION view_privs(text) returns table(username text, viewname regclass, privileges text[])
AS
$$
SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='v' and has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFRENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
</pre>
<br />
9. For Sequence Privilege following function can be use:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION sequence_privs(text) RETURNS table(username text, sequence regclass, privileges text[])
AS
$$
SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [
( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),
(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END)]),
(CASE WHEN has_table_privilege($1,c.oid,'USAGE') THEN 'UPDATE' ELSE NULL END) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='S' and
has_table_privilege($1,c.oid,'SELECT,UPDATE,USAGE') AND has_schema_privilege($1,c.relnamespace,'USAGE')
$$ language sql;
</pre>
<br />
User can also make join on above functions to view the privileges on objects something like given below:
<br />
<pre class="cpp" name="code">select * from (
select username,'SCHEMA' as object_type,schemaname as object_name,privieleges
FROM schema_privs('test_user')
UNION ALL
SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs
FROM table_privs('test_user')
) order by 2;
</pre>
<br />
Above will give table and schema privileges of a user test_user. Below is output:
<br />
<pre class="cpp" name="code">edb=# select * from
edb-# (select username,'SCHEMA' as object_type,schemaname as object_name,privieleges FROM schema_privs('test_user')
edb(# UNION ALL
edb(# SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs FROM table_privs('test_user')
edb(# ) order by 2;
username | object_type | object_name | privieleges
-----------+-------------+--------------------+------------------------
test_user | SCHEMA | pg_catalog | {USAGE}
test_user | SCHEMA | public | {CREATE,USAGE}
test_user | SCHEMA | information_schema | {USAGE}
test_user | SCHEMA | sys | {USAGE}
test_user | SCHEMA | dbo | {USAGE}
test_user | SCHEMA | test | {USAGE}
test_user | TABLE | test_id | {SELECT,UPDATE,DELETE}
(7 rows)
</pre>Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-18110817767223781592012-07-23T09:42:00.000-07:002012-07-23T09:43:53.677-07:00Monitor CPU and MEMORY percentage used by each process in PostgreSQL/PPAS 9.1PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives following information:
<br />
<pre class="cpp" name="code">1. datid: database OID
2. datname: database name
3. procpid: process ID
4. usesysid: user OID
5. usename: user name
6. application_name: application name
7. client_addr: client's address
8. client_hostname: host name (if available)
9. client_port: Clients port number
10. backend_start: time at which the server process started
11. xact_start: time at which current transaction started
12: query_start: time at which current query began execution started
13: waiting: process's waiting status
14. current_query: text of the current query.
</pre>
Above is good for monitoring sessions in postgresql. However, suppose user wants to know about percentage of CPU & Memory used by a particular session/user, then he can get this information by using plperlu function.<br />
<br />
To Create the pleperlu function, user needs to have plperlu language installed in DB.<br />
For creating the plperlu, connect to Database as super user and execute following command:<br />
<pre class="cpp" name="code">edb=# CREATE EXTENSION plperlu;
CREATE EXTENSION
</pre>
<br />
Now create following functions:
<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION get_pid_cpu_mem(int) returns table(PID INT,CPU_perc float,MEM_perc float)
as
$$
my $ps = "ps aux";
my $awk = "awk '{if (\$2==".$_[0]."){print \$2\":\"\$3\":\"\$4}}'";
my $cmd = $ps."|".$awk;
$output = `$cmd 2>&1`;
@output = split(/[\n\r]+/,$output);
foreach $out (@output)
{
my @line = split(/:/,$out);
return_next{'pid' => $line[0],'cpu_perc' => $line[1], 'mem_perc' => $line[2]};
return undef;
}
return;
$$ language plperlu;
</pre>
<b>Note::</b> Above function is made for PostgreSQL/PPAS running on Linux/Unix System.<br />
<br />
Now user can use above function with pg_stat_activity to monitor the percentage of cpu and memory used by particular user/process.
<br />
<br />
Following is one snapshot:
<br />
<pre class="cpp" name="code">edb=# select procpid,usename,application_name, get_pid_cpu_mem(procpid).cpu_perc,get_pid_cpu_mem(procpid).mem_perc,current_query from pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------------------------------------------------------------------------------------------------------------------------
procpid | 12991
usename | enterprisedb
application_name | psql
cpu_perc | 0
mem_perc | 0.6
current_query | select procpid,usename,application_name, get_pid_cpu_mem(procpid).cpu_perc,get_pid_cpu_mem(procpid).mem_perc,current_query from pg_stat_activity;
</pre>Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-84085407866021461302012-06-11T20:28:00.000-07:002012-06-11T20:29:07.693-07:00Creating user probe and alert in PEM 2.1This is also one type of thing in which people are very interested.
How to create probes other than PEM inbuilt probe? And how to create alert based on probe?<br />
Well answer is simple. Understand what probe is in PEM and then understand PEM Data detail. <br />
Probe in PEM requires following:
<br />
<pre class="cpp" name="code">1. SQL Code which can be use to gathering data by pemagent.
2. Table in pemdata schema, which will be use for storing Current status/data of SQL.
3. History table (specially in pemhistory schema) where all history data will reside.
</pre>
<br />
Suppose user wants to monitor the Slony Replication using PEM (since, PEM doesn't have slony replication moniotring), so user can do following:<br />
1. SQL Code which can be use for slony replication monitoring.<br />
We know that slony replication monitoring can be done using view sl_status of slony. So, user can create a SQL Code as given below for replication.
<br />
<pre class="cpp" name="code">select '_test_slony' as schema_name, st_origin,st_received, st_lag_num_events , st_lag_time from _test_slony.sl_status
</pre>
Now, you must be thinking why I am including Slony SCHEMA name in SQL Code. Well, reason is that slony_schema name represents Slony Cluster name and if user is having more than one slony replication in Database, then those can be monitor with small modification of SQL Code. Also, PEM data table and PEM History can be use for monitoring multiple slony replication cluster.<br />
<br />
Lets start with creating probe based on SQL Code:<br />
Standard way of creating probe in Postgres Enterprise Manager:<br />
1. Insert the detail of probe in probe table with SQL Code for monitoring:
<br />
<pre class="cpp" name="code">INSERT INTO probe(display_name,
internal_name,
collection_method,
target_type_id,
applies_to_id,
probe_code,
enabled_by_default, default_execution_frequency,default_lifetime,any_server_version,force_enabled,probe_key_list)
VALUES('slon replication monitor','slony_replication','s',300,600,'select ''_test_slony'' as schema_name, st_origin,st_received, st_lag_num_events , st_lag_time from _test_slony.sl_status',false,300,180,true,false,ARRAY['st_origin','st_received']);
</pre>
Comlumn Description of probe table is given below:<br />
<b>display_name:</b> Name as presented in Alerting Box.<br />
<b>internal_name: </b>Internal Name of probe to PEM server. Based on that function: pem.create_data_and_history_tables(), creates pemdata and pemhistory table<br />
<b>collection_method:</b> is Collection based on SQL: 's' or based on internal code of pemagent: 'i'
<b>target_type_id:</b> Target type id, Type id and its description below:
<br />
<pre class="cpp" name="code"> Global : 50
Agent : Agent
Server : 200
Database: 300
Schema : 400
Table : 500
Index : 600
Sequence: 700
Function: 800
</pre>
If probe is for database level then target_type_id would be 300.<br />
<b>applies_to_id:</b> probe is apply to which type id, i.e if user can make database level probe which can be apply to table (like IndexSize can be database level and can be table level alert).<br />
<b>probe_code: </b> SQL Code/function to collect data
<b>e</b><br />
<b>nabled_by_default:</b> if true then it will be enable for all pemagent servers<br />
<b>default_execution_frequency:</b> Interval<br />
<b>default_lifetime:</b> Data retention period<br />
<b>any_server_version: </b>Is it PPAS specific alert or PG/PPAS both (true/false)<br />
<b>force_enabled:</b> By default enable (true/false).<br />
<b>probe_key_list:</b> Key columns<br />
<br />
2. Insert the detail of data column, returns by SQL Code, in probe column, as given below:
<br />
<pre class="cpp" name="code">INSERT INTO pem.probe_column (probe_id, internal_name, display_name, display_position, classification,
sql_data_type, unit_of_value, calculate_pit, discard_history, pit_by_default, is_graphable)
SELECT
(SELECT max(id) FROM PEM.probe), v.internal_name, v.display_name, v.display_position, v.classification,
v.sql_data_type, v.unit_of_value, v.calculate_pit, v.discard_history, v.pit_by_default, v.is_graphable FROM (
VALUES('database_name','Database Name',1,'k','text','',false,false,false,false),
('schema_name','Slony Cluster',2,'k','text','',false,false,false,false),
('st_origin','Master Node ID',3,'k','INTEGER','',false,false,false,false),
('st_received','Slave Node ID',4,'k','INTEGER','',false,false,false,false),
('st_lag_num_events','Lag Events',5,'m','INTEGER','',false,false,false,false),
('st_lag_time','Lag Interval',6,'m','INTERVAL','',false,false,false,false)
)
v(internal_name, display_name, display_position, classification,
sql_data_type, unit_of_value, calculate_pit, discard_history, pit_by_default, is_graphable);
</pre>
Description of columns are given below:<br />
<b>probe_id :</b> Assigned probe_id for column (its max(id) of pem.probe column.<br />
<b>internal_name :</b> Internal name to PEM server.<br />
<b>display_name : </b>Column Display name to user.<br />
<b>display_position :</b> Position of column in SQL Code.<br />
<b>classification : </b>If column consider as primary key i.e based on this record can be identified, then value would 'k' else 'm'.<br />
<b>sql_data_type : </b>Data type of Column<br />
<b>unit_of_value : </b>Unit of data of column.<br />
<b>calculate_pit : </b> Point in time data<br />
<b>discard_history : </b>Discard any history.<br />
<b>pit_by_default : </b>Default Point in time representation true/false<br />
<b>is_graphable :</b> Can be use for graph (always keep false).<br />
<br />
3. Now use PEM server function to create data and history table in pemdata and pemhistory schema of PEM server.
<br />
<pre class="cpp" name="code">SELECT pem.create_data_and_history_tables();
</pre>
Above will create table with internal_name mentioned in probe_column, like pemdata.slony_replication and pemhistory.slony_replication.
<br />
<br />
Above are three simple steps to create probe in PEM Server. Now, user can see that if you know SQL language, you can create probe in PEM. To verify the probe you can see the probe in PEM Client:<br />
<pre class="cpp" name="code">Open PEM Client -> Go to PEM Server directory (in Left pane) -> Connect to PostgreSQL Cluster -> expand the databases -> Right click on any database -> select Probe Configuration.
which will popup "Probe configuration" and you would be able to see the slony replication.
</pre>
<br />
Since, we have created probe and based on probe, we can create template alert.<br />
Creating Template Alert is simple. For new template alert, you have to do following:<br />
1. Identify the right SQL code for monitoring based on probe, as given below for slony replication:
<br />
<pre class="cpp" name="code">SELECT extract ('epoch' from st_lag_time) from pemdata.slony_replication WHERE server_id=${server_id} AND database_name='${database_name}' AND st_origin='${param_1}' AND st_received='${param_2}' and st_lag_time >=interval '1 sec' AND schema_name='_test_slony'
</pre>
In above I am using epoch function to calculate seconds and based on that I can have alert which would be having threshold value for Low, Medium and High alert.<br />
<br />
2. Use PEM server function, pem.create_alert_template(), to create template alert as given below:
<br />
<pre class="cpp" name="code">SELECT pem.create_alert_template('Slony Replication','Slony Replication Monitoring Alert',$SQL$ SELECT extract ('epoch' from st_lag_time) from pemdata.slony_replication WHERE server_id=${server_id} AND database_name='${database_name}' AND st_origin='${param_1}' AND st_received='${param_2}' and st_lag_time >=interval '1 sec' AND schema_name='_test_slony' $SQL$,300,ARRAY['st_origin','st_received'],ARRAY['INTEGER','INTEGER']::alert_param_type[],NULL,'seconds',ARRAY['slony_replication'],2);
</pre>
<br />
Argument description of pem.create_alert_template() function is given below:<br />
<b>1.name :</b> Name of alert in single quotes<br />
<b>2.description :</b> Small Description of Alert<br />
<b>3.sql :</b> SQL code for getting threshold<br />
<b>4.object_type : </b>Alert is of Agent LevelServer Level,<br />
<b>5.param_names : </b>SQL Code is dependent on any parameter name, (like in my SQL Code, its depend '${param_1}' (origin) and '{param_2}' (subscriber node id)<br />
<b>6.param_types : </b>Data type of each parameter (As per SQL code for slony, this would be NULL)<br />
<b>7.param_units :</b> Assigned unit<br />
<b>8.threshold_unit :</b> Unit of threshold (since I am doing Checking the Time lag, there it would seconds).<br />
<b>9.probe_dependency_list:</b> Dependency on Pemdata table (Its slony_replication)<br />
<b>10.snmp_oid :</b> Oid for snmp<br />
<b>11.applicable_on_server:</b> Is this applicable for Advanced Server or ALL<br />
<b>12.default_check_frequency:</b> Interval<br />
<b>13.default_history_retention:</b> data retention in number of days.<br />
<br />
Thats it! Now, you can see creating new user defined alert and probe in PEM is very simple. Only thing which user has to do is to focus on identifying right SQL Code for alert/probe.<br />
<br />
Have fun with Postgres Enterprise Manager!Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-79060179022629569342012-06-04T13:01:00.000-07:002012-06-04T13:01:21.417-07:00Automating Binding of Servers with PEM Agent in Postgres Enterprise Manager 2.1This is second post of Postgres Enterprise Manager 2.1 series.<br />
<br />
Question which people ask, Is Postgres Enterprise Manager tool useful for Companies, which provide services for PostgreSQL? Answer is yes. What about companies, which provide infrastructure support, where they provide server and PostgreSQL database, for them, is this is a good tool for monitoring PostgreSQL? Answer is yes, you can use it.<br />
<br />
Companies, which are giving Infrastructure support and providing Database As service always want everything to be automated. i.e with provisioning server, server should have installed PostgreSQL and its components plus they want automatic installation of pemagent, which is also acheivable. However, they stumped on Automatic Bidning of PEM Agent with PostgreSQL.<br />
<br />
For binding PostgreSQL with PEM Agent, Simple Method is using PEM Client.
In PEM Client, Add a PostgreSQL in PEM Directory and then user can bind the PEM agent with PostgreSQL Cluster.<br />
<pre class="cpp" name="code">File -> Add Server -> which will popup screen for "New serve Registeration" -> After adding server information (Also click on Store PEM Server)
</pre>
Then in "New server Registeration Window" ->
<br />
<pre class="cpp" name="code"> Click on tab "PEM Agent" -> Choose agent from drop down window of "Bound Agent", Then user can include the information. </pre>
Well above is one method. However for Company which provides infrastructure support doesn't want to do this manual work. They want this activity to be automated.
For automating this process, user can do following:<br />
<br />
1. Install the PEM agent in unattended mode. For detail of installing pemagent in unattended mode, user can use command like:
<br />
<pre class="cpp" name="code">./pem_agent-2.1.0.2-linux-x64.run --prefix /opt/PEM --mode unattended --pghost 'pem server hostname' --pguser 'pem server username' --pgpassword 'password' --agent_description 'Agent Hostname'
</pre>
2. Create .pgpass file in root/admin user home directory. Like on linux root home directory is /root.
<br />
<pre class="cpp" name="code">
# cat .pgpass
#hostname:port:database:username:password
*:5432:*:postgres:'your postgres password'
</pre>
Change the permission on .pgpass:
<br />
<pre class="cpp" name="code">
chmod 600 .pgpass
</pre>
3. After creating the .pgpass file in home directory, execute following SQL:
<br />
<pre class="cpp" name="code">/opt/PEM/agent/bin/edb-psql -h 'PEM Server hostname' -p 5432 -c "
INSERT INTO pem.server(description,server,port,database) VALUES('New server','IP Address',5432,'postgres',2);" -U postgres
/opt/PEM/agent/bin/edb-psql -h 'PEM Server hostname' -p 5432 -c "INSERT INTO pem.server_option(server_id,pem_user,username,server_group) VALUES((SELECT id FROM pem.server WHERE server='IP address' AND active=true),'postgres','postgres','PEM Server Directory');" -U postgres
/opt/PEM/agent/bin/edb-psql -h 'PEM Server hostname' -p 5432 -c "INSERT INTO pem.agent_server_binding(agent_id,server_id,server,port,username,database)
VALUES((SELECT id FROM pem.agent WHERE description ='Agent Hostname' AND active=true),(SELECT id FROM pem.server WHERE server='IP address' AND active=true),5432,'postgres','postgres');" -U postgres
</pre>
After executing above SQLs, you have successfully binded agent with Server for monitoring.<br />
User can also include above steps in shell script and can execute while provisioning new server for their client.<br />
<br />
Have Fun!Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com2tag:blogger.com,1999:blog-3301074144512804889.post-47523573318351580532012-06-02T08:56:00.000-07:002012-06-02T09:05:31.281-07:00Types of Alerts in Postgres Enterprise Manager: 2.1.Its being two months, that I didn't blog on any topic. So, I thought to share some information on PEM.<br />
<br />
This post is for users, who always look for type of inbuild alerts of PEM. In this Blog, I am listing categories of Alerts and List of alerts in each category, which one can find in Postgres Enterprise Manager 2.1.<br />
Also, if you have list of alerts then you can decide which alert you would like to configure.<br />
<br />
Postgres Enterprise Manager (PEM) is very useful tool for monitoring PostgreSQL and it has all the alerts which is require for monitoring postgresql.
User can define this alerts in following categories:
<br />
<pre class="cpp" name="code">1. Server Level Alerts.
2. PG Cluster Level Alerts.
3. Database Level Alerts.
4. Schema Level Alerts
5. Table Level Alerts.
</pre>
All the alerts which has been made/defined keeping in mind of postgreSQL. Let see what are the alerts in each categories:<br />
<b>1. Server Level Alerts:</b> These alerts are made for monitoring Server Components like Memory,Disk and CPU and Server Level monitoring of PostgreSQL:
<br />
<pre class="cpp" name="code"> 1. Average table bloat on host (Bloats impact CPU and Disk)
2. CPU utilization
3. Database size on host
4. Disk Available
5. Disk busy percentage
6. Disk Consumption
7. Disk consumption percentage
8. Free memory percentage
9. Highest table bloat on host
10. Load Average (15 minutes)
11. Load Average (1 minute)
12. Load Average (5 minutes)
13. Load Average per CPU Core (15 minutes)
14. Load Average per CPU Core (1 minutes)
15. Load Average per CPU Core (5 minutes)
16. Memory used percentage
17. Most used disk percentage
18. Number of CPUs running higher than a threshold
19. Swap consumption
20. Swap consumption percentage
21. Table size on host
22. Total table bloat on host
</pre>
User can see above alerts covered server Monitoring which includes: Memory,Disk,CPU and Bloats & Biggest table on host.<br />
<b><br /></b><br />
<b>2. PG Cluster Level Alert: </b>These alerts are made for Monitoring PostgreSQL, Cluster Level. i.e. Number of Connections, Database Level Stats, User monitoring etc… Following is list of PG Cluster Level alerts:
<br />
<pre class="cpp" name="code"> 1. A user expires in N days
2. Average table bloat in server
3. Buffers allocated per second
4. Buffers written per second
5. Committed transactions percentage
6. Connections in idle-in-transaction state
7. Connections in idle-in-transaction state, as a percentage of max_connections
8. Connections in idle state
9. Database size in server
10. Dead Tuples
11. Dead tuples percentage
12. Function Count
13. Highest table bloat in server
14. Hot update percentage
15. Index Scans
16. Index size as a percentage of table size
17. InfiniteCache buffers hit percentage
18. Largest index by table-size percentage
19. Largest table (by multiple of unbloated size)
20. Last Analyze
21. Last AutoAnalyze
22. Last AutoVacuum
23. Last Vacuum
24. Live Tuples
25. Long-running autovacuums
26. Long-running idle connections
27. Long-running idle connections and idle transactions
28. Long-running idle transactions
29. Long-running queries
30. Long-running transactions
31. Long-running vacuums
32. Number of prepared transactions
33. Number of WAL files
34. Percentage of buffers written by backends
35. Percentage of buffers written by backends over last N minutes
36. Percentage of buffers written by checkpoint
37. Sequence Count
38. Sequential Scans
39. Shared buffers hit percentage
40. Table Count
41. Table size in server
42. Total connections
43. Total connections as percentage of max_connections
44. Total table bloat in server
45. Tuples deleted
46. Tuples fetched
47. Tuples hot updated
48. Tuples inserted
49. Tuples returned
50. Tuples updated
51. Ungranted locks
52. Unused, non-superuser connections
53. Unused, non-superuser connections as percentage of max_connections
</pre>
<b><br /></b><br />
<b>3. Database Level Alerts: </b>These alerts for Monitoring Specific Database in PostgreSQL Cluster. This is useful when you have database, which is important for your Bussiness and monitoring of that database is important for you:
<br />
<pre class="cpp" name="code"> 1. Average table bloat in database
2. Committed transactions percentage
3. Connections in idle-in-transaction state
4. Connections in idle-in-transaction state, as a percentage of max_connections
5. Connections in idle state
6. Database Frozen XID
7. Database size
8. Dead Tuples
9. Dead tuples percentage
10. Function Count
11. Highest table bloat in database
12. Hot update percentage
13. Index Scans
14. Index size as a percentage of table size
15. InfiniteCache buffers hit percentage
16. Largest index by table-size percentage
17. Largest table (by multiple of unbloated size)
18. Last Analyze
19. Last AutoAnalyze
20. Last AutoVacuum
21. Last Vacuum
22. Live Tuples
23. Long-running autovacuums
24. Long-running idle connections
25. Long-running idle connections and idle transactions
26. Long-running idle transactions
27. Long-running queries
28. Long-running transactions
29. Long-running vacuums
30. Sequence Count
31. Sequential Scans
32. Shared buffers hit percentage
33. Table Count
34. Table size in database
35. Total connections
36. Total connections as percentage of max_connections
37. Total table bloat in database
38. Tuples deleted
39. Tuples fetched
40. Tuples hot updated
41. Tuples inserted
42. Tuples returned
43. Tuples updated
44. Ungranted locks
</pre>
<b><br /></b><br />
<b>4. Schema Level Alerts:</b> User can also configure alerts for specific schema in Database. This is important when you have a schema, related to important Business Objects and you have to monitor the performance of tables in schema. List of those alerts is given below:
<br />
<pre class="cpp" name="code"> 1. Average table bloat in schema
2. Dead Tuples
3. Dead tuples percentage
4. Function Count
5. Highest table bloat in schema
6. Hot update percentage
7. Index Scans
8. Index size as a percentage of table size
9. Largest index by table-size percentage
10. Largest table (by multiple of unbloated size)
11. Last Analyze
12. Last AutoAnalyze
13. Last AutoVacuum
14. Last Vacuum
15. Live Tuples
16. Sequence Count
17. Sequential Scans
18. Table Count
19. Table size in schema
20. Total table bloat in schema
21. Tuples deleted
22. Tuples hot updated
23. Tuples inserted
24. Tuples updated
</pre>
<b><br /></b><br />
<b>5. Table Level Alerts:</b> User can also create alert table level. Some times, user are interested in monitoring important/specific table which has business importance. For them these alerts are important for maintaining the performance of PG.
<br />
<pre class="cpp" name="code"> 1. Dead Tuples
2. Dead tuples percentage
3. Hot update percentage
4. Index Scans
5. Index size as a percentage of table size
6. Last Analyze
7. Last AutoAnalyze
8. Last AutoVacuum
9. Last Vacuum
10. Live Tuples
11. Row Count
12. Sequential Scans
13. Table bloat
14. Table Frozen XID
15. Table size
16. Table size as a multiple of ubloated size
17. Tuples deleted
18. Tuples hot updated
19. Tuples inserted
20. Tuples updated
</pre>
You can see that Postgres Enterprise Manager covers PostgreSQL monitoring from all aspects which directly/indirectly responsible for PostgreSQL Performance/Monitoring.<br />
<br />
Now, reader of my blog must be thinking, these are the list of alerts. How can they get more information/description on these alerts. Answer is simple, you can get more detail about above alerts by three methods:<br />
<b>1. Using PEM Client HELP</b>
<br />
<pre class="cpp" name="code"> Open PEM Client -> Go to Help</pre>
<b><br /></b><br />
<b>2. Using Alerting:</b>
<br />
<pre class="cpp" name="code"> Open PEM Client ->
For server Level, go to -> PEM Agents -> Right click on particular agent -> click on Alerting
</pre>
For PG Cluster Level,
<br />
<pre class="cpp" name="code">go to -> PEM Server Directory -> Right Click on particular PG Cluster -> select Alerting </pre>
Similarly For Database alerts and Table Alerts.<br />
<br />
<b>3. Using SQL:</b>
Connect to PEM Server Database:
<br />
<pre class="cpp" name="code"> psql -p 5432 -U postgres pem
</pre>
And use following SQLs:<br />
Server Level:
<br />
<pre class="cpp" name="code">SELECT row_number() over (order by display_name)||'. '|| at.display_name AS display_name,description FROM pem.alert_template at WHERE (at.object_type = 100) ORDER BY at.display_name;
</pre>
<br />
CLuster Level:
<br />
<pre class="cpp" name="code">SELECT row_number() over (order by display_name)||'. '|| at.display_name, description FROM pem.alert_template at WHERE (at.object_type = 200) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;
</pre>
DB Level:
<br />
<pre class="cpp" name="code">SELECT row_number() over (order by display_name)||'. '|| at.display_name, description FROM pem.alert_template at WHERE (at.object_type = 300) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;
</pre>
<br />
Schema Level:
<br />
<pre class="cpp" name="code">SELECT row_number() over (order by display_name)||'. '|| at.display_name, description FROM pem.alert_template at WHERE (at.object_type = 400) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;
</pre>
<br />
Table Level:
<br />
<pre class="cpp" name="code">SELECT row_number() over (order by display_name)||'. '|| at.display_name, description FROM pem.alert_template at WHERE (at.object_type = 500) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;
</pre>
Have Fun! and Have perfect Monitoring of PostgreSQL.Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com1tag:blogger.com,1999:blog-3301074144512804889.post-17836806005600682282012-03-21T12:37:00.000-07:002012-03-21T12:37:00.418-07:00READ-ONLY user,READ-ONLY Database,READ-ONLY backup user in PostgreSQL/PPASThis has been asked me many times. Many users has asked me how they can create READ-ONLY user, READ-ONLY Database or How they can create backup user which can be use only for backup (no modification)<br />
<br />
Well answer is in parameter called default_transaction_read_only.<br />
<br />
If you want to make a user READ-ONLY, then you can follow steps given below:<br />
1. CREATE normal user.<br />
2. Use ALTER USER command to set this parameter for this user as given below:<br />
<pre class="cpp" name="code">ALTER USER <username> set default_transaction_read_only = on;
</pre>3. GRANT SELECT to this user for tables/object using GRANT. And you are good to go.<br />
Below is snapshot:<br />
<pre class="cpp" name="code">postgres=# create user readonly password 'test';
CREATE ROLE
postgres=# alter user readonly set default_transaction_read_only = on;
ALTER ROLE
postgres=# GRANT select on employees to readonly;
GRANT
edbs-MacBook-Pro:data postgres$ psql -U readonly -W
Password for user readonly:
psql (9.1.1)
Type "help" for help.
postgres=> select * from employees ;
employee_name | entity_name
---------------+-------------
Smith | HR
Jones | HR
Taylor | SALES
Brown | SALES
(4 rows)
postgres=> CREATE table test_readonly(id numeric);
ERROR: cannot execute CREATE TABLE in a read-only transaction
postgres=>
</pre>Similarly, If you want to make a Database READ-ONLY, then you can use following command<br />
<pre class="cpp" name="code">ALTER DATABASE <database name> set default_transaction_read_only=on;
</pre>Below is snapshot:<br />
<pre class="cpp" name="code">postgres=# CREATE database readonly;
CREATE DATABASE
postgres=# alter database readonly set default_transaction_read_only = on;
ALTER DATABASE
postgres=# \q
edbs-MacBook-Pro:data postgres$ psql readonly
psql (9.1.1)
Type "help" for help.
readonly=# create table test_readonly(id numeric);
ERROR: cannot execute CREATE TABLE in a read-only transaction
readonly=#
</pre>Now, if you want a seperate backup user which you want to use for Online/Hot Backup,Logical Backup (using pg_dump), then you can create a super user with default_transaction_read_only = on and can use it for backup purpose. As given below:<br />
<pre class="cpp" name="code">CREATE USER backupuser SUPERUSER password 'backupuser';
ALTER USER backupuser set default_transaction_read_only = on;
</pre>Using default_transaction_read_only parameter, user can also make a Session Readonly by executing following command:<br />
<pre class="cpp" name="code">set default_transaction_read_only=on;
</pre><br />
I hope above would help someone who is interested in having READ-ONLY user,database or backupuser.Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com4tag:blogger.com,1999:blog-3301074144512804889.post-70356887569228219552012-02-13T20:29:00.000-08:002012-02-13T20:29:02.556-08:00Partition Syntax Support in PPAS 9.1In Series of New Features in Advanced Server 9.1, today I tought to write about Parition Table Syntax Supported in PPAS. In PostgreSQL and till PPAS 9.0, user has to follow method given below for partitioning a table:<br />
<b>1. CREATE PARENT Table,<br />
2. Create Child tables using Inherit feature<br />
3. Create Trigger on Partition on Parent, so that it can re-direct insert to Right Partition. <br />
4. And if user has to add new child table, then it has to do 2 and 3 steps again. </b><br />
<br />
Now, in PPAS 9.0, user doesn't have to perform above activities. PPAS 9.0 supports PARTITION TABLE syntax.<br />
<br />
Lets see how PARTITION syntax in PPAS can make users/DBAs life easier.<br />
<br />
We know that PPAS/PostgreSQL supports two types of partition (Range and List). So, we will see how its simple with CREATE PARTITION SYNTAX.<br />
Lets CREATE RANGE PARTITION as we used to do in PPAS 9.0/PostgreSQL<br />
1. Create Master table as given below:<br />
<pre class="cpp" name="code">CREATE TABLE partition_master(id numeric primary key,val text);
CREATE TABLE partition_child1(CHECK (id > 0 and id <=10)) inherits(partition_master);
CREATE TABLE partition_child2(CHECK (id >10 and id <=20)) inherits(partition_master);
</pre>
2. Then create check constriants on Both Child tables
<pre class="cpp" name="code">alter table partition_child1 add primary key (id);
alter table partition_child2 add primary key (id);
</pre>3. Then Create Trigger, which redirect Inserts to right master:
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION part_trig_insrt() RETURNS trigger
AS
$$
BEGIN
IF TG_OP='INSERT' THEN
IF NEW.id >0 and NEW.id <=10 THEN
INSERT INTO partition_child1 VALUES(NEW.*);
ELSIF NEW.id >10 and NEW.id <=20 THEN
INSERT INTO partition_child2 VALUES(NEW.*);
ELSE
RAISE 'inserted partition key doesnt map to any partition';
END IF;
END IF;
END;
$$ language plpgsql;
CREATE TRIGGER partition_trig_insert BEFORE INSERT ON partition_master FOR EACH ROW execute procedure part_trig_insrt();
</pre>
similarly you have to write trigger which can handle partition key update.
And wheneven you want to add new partition update the trigger function and create new partition table etc.
Now, in 9.1AS on-wards, user can run single command for partition and PPAS will take care of all things as given below:
<pre class="cpp" name="code">CREATE TABLE partition_master (id numeric primary key,val text)
PARTITION BY RANGE(id)
(PARTITION partition_child1 VALUES LESS THAN (11),
PARTITION partition_child2 VALUES LESS THAN (21));
edb=# insert into partition_master values(1,'First');
INSERT 0 0
edb=# insert into partition_master values(11,'Eleventh');
INSERT 0 0
edb=# select * from partition_master;
id | val
----+----------
1 | First
11 | Eleventh
(2 rows)
edb=# select * from only partition_master_partition_child1;
id | val
----+-------
1 | First
(1 row)
edb=# select * from partition_master_partition_child2;
id | val
----+----------
11 | Eleventh
(1 row)
</pre>That was easy. With single command you can have your partition table in PPAS.
Suppose later, user wants to add one more partition then he can execute following single command:
<pre class="cpp" name="code">ALTER TABLE partition_master add partition partition_child3 VALUES LESS THAN (31);
edb=# insert into partition_master values(30,'Thirty');
INSERT 0 0
edb=# select * from partition_master;
id | val
----+----------
1 | First
11 | Eleventh
30 | Thirty
(3 rows)
edb=# select * from partition_master_partition_child3;
id | val
----+--------
30 | Thirty
(1 row)
</pre>Thats simple. isn't?
With this PPAS 9.1 Partition Syntax also allows swaping an existing table with a partition or subpartition, as given below:
<pre class="cpp" name="code">CREATE TABLE single_table (id numeric primary key,val text)
insert into single_master select generate_series(1,10);
INSERT 0 10
ALTER TABLE partition_master
EXCHANGE PARTITION partition_child1
WITH TABLE single_table;
</pre>Other syntax which are supported is given below:
<pre class="cpp" name="code">1. ALTER TABLE… ADD PARTITION
2. ALTER TABLE… ADD SUBPARTITION
3. ALTER TABLE… DROP PARTITION
4. ALTER TABLE… DROP SUBPARTITION
5. ALTER TABLE… SPLIT PARTITION
6. ALTER TABLE… SPLIT SUBPARTITION
7. ALTER TABLE… TRUNCATE PARTITION
8. ALTER TABLE… TRUNCATE SUBPARTITION
9. ALTER TABLE… EXCHANGE PARTITION
10. ALTER TABLE… MOVE PARTITION
11. ALTER TABLE… RENAME PARTITION
</pre>Support of above partition syntaxes have really made management of partition table easier in PPAS 9.1!Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com2tag:blogger.com,1999:blog-3301074144512804889.post-89309191029122965102012-02-10T12:36:00.000-08:002012-02-10T12:36:51.935-08:00Virtual Private Database (VPD) in PPAS 9.1Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1<br />
<br />
Virtual Private Database (VPD) is a feature which enables Administrator to create security around actual data (i.e row/columns) so that multiple users can access data which is relevant to them. Steps which is require to create Virtual Private database is given below:<br />
<strong><br />
1. Create an Application Context<br />
2. Create security policies functions<br />
3. Apply security policies to tables<br />
</strong><br />
Lets see how user can implement it in Advanced Server. <br />
1. Setup an environment as given below:<br />
<pre class="cpp" name="code">CREATE user merry identified by edb;
CREATE user john identified by edb;
CREATE TABLE public.john_merry(userid varchar2(200),val numeric);
grant select,update,delete,insert on john_merry to john;
grant select,update,delete,insert on john_merry to merry;
</pre>2. Now create a Policy Function as given below:<br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION verify_user (
p_schema VARCHAR2,
p_object VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
RETURN 'userid = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
END;
3. Apply Security Policy using Policy Functions as given below:
DECLARE
v_object_schema VARCHAR2(30) := 'public';
v_object_name VARCHAR2(30) := 'john_merry';
v_policy_name VARCHAR2(30) := 'secure_data';
v_function_schema VARCHAR2(30) := 'enterprisedb';
v_policy_function VARCHAR2(30) := 'verify_user';
v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
v_update_check BOOLEAN := TRUE;
v_enable BOOLEAN := TRUE;
BEGIN
DBMS_RLS.ADD_POLICY(
v_object_schema,
v_object_name,
v_policy_name,
v_function_schema,
v_policy_function,
v_statement_types,
v_update_check,
v_enable
);
END;
</pre><br />
Now, lets see how it works.<br />
First insert some records for John and Merry as given below:<br />
1. Connect as John user and do some insert as john, as given below:<br />
<pre class="cpp" name="code">edb=> insert into john_merry values('john',1);
INSERT 0 1
edb=> insert into john_merry values('john',2);
INSERT 0 1
edb=> insert into john_merry values('john',3);
INSERT 0 1
</pre>2. Now connect as merry and insert some records as merry:<br />
<pre class="cpp" name="code">edb=> insert into john_merry values('merry',1);
INSERT 0 1
edb=> insert into john_merry values('merry',2);
INSERT 0 1
edb=> insert into john_merry values('merry',3);
INSERT 0 1
</pre>Ok. Now we have some data for both users. lets see how VPD works:<br />
1. Connect as John User and try to insert some record for merry:<br />
<pre class="cpp" name="code">edb=> insert into john_merry values('merry',4);
ERROR: policy with check option violation
DETAIL: Policy predicate was evaluated to FALSE with the updated values
</pre>hmm this is expected, VPD security policy won't allow me to do this activity.<br />
<br />
2. Now try to update Merry data as John User:<br />
<pre class="cpp" name="code">edb=> update john_merry set val=4 where val=3 and userid='merry';
UPDATE 0
</pre>No rows updated. This is expected since as per security policy merry's data is not visible to john. However, john can update his record as given below:<br />
<pre class="cpp" name="code">edb=> update john_merry set val=4 where val=3 and userid='john';
UPDATE 1
</pre>3. Now, lets execute SELECT query on table to view data:<br />
<pre class="cpp" name="code">edb=> select current_user;
current_user
--------------
john
(1 row)
edb=> select * from john_merry ;
userid | val
--------+-----
john | 1
john | 2
john | 4
(3 rows)
</pre><br />
As you can see, john can see his records not merry's. Similary user can try for Merry. Below is a snapshot of SELECT query executed by merry:<br />
using dumb terminal settings.<br />
<pre class="cpp" name="code">edb=> select current_user;
current_user
--------------
merry
(1 row)
edb=> select * from john_merry ;
userid | val
--------+-----
merry | 1
merry | 2
merry | 3
(3 rows)
</pre>Interesting!!Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com2tag:blogger.com,1999:blog-3301074144512804889.post-34635435249712677972011-12-17T23:53:00.000-08:002011-12-17T23:57:14.730-08:00SMTP Exceptions in PPAS9.0Till PPAS 9.0, UTL_SMTP package has no specific named Exceptions for Transient_error, Invalid_operation and Permanent Error. However, Oracle provide following types of named exceptions for SMTP:<br />
<b>1. INVALID_OPERATION:</b><br />
Raised when an invalid operation is made. In other words, calling API other than write_data(), write_raw_data() or close_data() after open_data() is called, or calling write_data(), write_raw_data() or close_data() without first calling open_data().<br />
<br />
<b>2. TRANSIENT_ERROR:</b><br />
Raised when receiving a reply code in 400 range.<br />
<br />
<b>3. PERMANENT_ERROR:</b><br />
Raised when receiving a reply code in 500 range.<br />
<br />
Oracle users who use SMTP packages, they also use above exceptions extensively to track the SMTP Error/Message and perform some handling on basis of exception.<br />
<br />
Since, till now PPAS doesn't have these exceptions, therefore people stuck on finding workaround for such exception. <br />
<br />
To make this easy, I did some research and made following workaround, which user can use in PPAS for SMTP Exceptions as they do in Oracle.<br />
<br />
Following are workarounds for SMTP named Exceptions:<br />
<br />
There is no direct way to trap SMTP reply code, However PPAS SQLERRM does have description of SMTP Reply code. So using SQLERRM, user can implement or defined named Exceptions, which is pretty simple and straight.<br />
<br />
Easiest way of doing is to create functions, which can decide if exception error is TRANSIENT, PERMANENT or INVALID_OPERATION. So, user can create following functions in PPAS:<br />
<br />
<b>1. Function For transient Error Message:</b><br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION SMTP_TRANSIENT_ERROR(text) RETURNS boolean
As
$$
SELECT CASE WHEN
$1 ILIKE '%Service not available%' OR
$1 ILIKE '%Requested mail action not taken%' OR
$1 ILIKE '%Requested action terminated%' OR
$1 ILIKE '%Requested action not taken%' OR
$1 ILIKE '%You have no mail%' OR
$1 ILIKE '%TLS not available due to temporary reason%. Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Unable to queue messages for %' OR
$1 ILIKE '%Node%not allowed%' THEN true
ELSE false END from dual;
$$ language sql;
</pre><br />
<b>2. Function for Permanent Error Message:</b><br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION SMTP_PERMANENT_ERROR(text) RETURNS Boolean
AS
$$
SELECT CASE WHEN
$1 ILIKE '%Syntax error%command unrecognized%' OR
$1 ILIKE '%Syntax error in parameters or arguments%' OR
$1 ILIKE '%Command not implemented%' OR
$1 ILIKE '%Bad sequence of commands%' OR
$1 ILIKE '%Command parameter not implemented%' OR
$1 ILIKE '%does not accept mail%' OR
$1 ILIKE '%Must issue a STARTTLS command first. Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Authentication mechanism is too weak%' OR
$1 ILIKE '%Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Requested action not taken%' OR
$1 ILIKE '%User not local; please try%' OR
$1 ILIKE '%Requested mail action terminated: exceeded storage allocation%' OR
$1 ILIKE '%Requested action not taken:%' OR
$1 ILIKE '%Transaction failed%' THEN true ELSE false END FROM DUAL;
$$ language sql
</pre><b>3. Function for INVALID_OPERATION </b><br />
<pre class="cpp" name="code">CREATE OR REPLACE FUNCTION SMTP_INVALID_OPERATION(TEXT) RETURNS BOOL
AS
$$ SELECT CASE WHEN $ ILIKE '%INVALID%OPERATION%STATE%' THEN TRUE ELSE FALSE END FROM DUAL;
$$ Language sql;
</pre><br />
Below are some working examples:<br />
<br />
<b>In Oracle (INVALID_OPERATION) :</b><br />
<pre class="cpp" name="code">CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := '127.0.0.1'; -- local database host
BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me
utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;
SQL> exec send_mail(msg_to=>'vibhor.aim@gmail.com', -
msg_subject => 'Hello from Oracle', -
msg_text => 'This is the body of the message'-
);
Invalid Operation in Mail attempt
using UTL_SMTP.
PL/SQL procedure successfully completed.
</pre>In PPAS:<br />
<pre class="cpp" name="code">CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'PPAS 9.0';
mailhost VARCHAR2(30) := '127.0.0.1'; -- local database host
BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me
utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN OTHERS THEN
IF SMTP_INVALID_OPERATION(SQLERRM) THEN
DBMS_OUTPUT.PUT_LINE('Invalid Operation in Mail attempt using UTL_SMTP.');
ELSIF SMTP_TRANSIENT_ERROR(SQLERRM) THEN
DBMS_OUTPUT.PUT_LINE(' Temporary e-mail issue - try again');
ELSIF SMTP_PERMANENT_ERROR(SQLERRM) THEN
DBMS_OUTPUT.PUT_LINE(' Permanent Error Encountered.');
ELSE
DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE||' Error Message: '||SQLERRM);
END IF;
END;
EDB-SPL Procedure successfully completed
edb=# exec send_mail(msg_to=>'vibhor.aim@gmail.com',
edb(# msg_subject => 'Hello from PPAS',
edb(# msg_text => 'This is the body of the message');
Invalid Operation in Mail attempt using UTL_SMTP.
EDB-SPL Procedure successfully completed
</pre><br />
<br />
<b>Example 2: In Oracle (UTL_SMTP.TRANSIENT_ERROR ):</b><br />
<pre class="cpp" name="code">CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := '192.168.23.25'; -- local database host
BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me
utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;
SQL> exec send_mail(msg_to=>'vibhor.aim@gmail.com', -
msg_subject => 'Hello from Oracle', -
msg_text => 'This is the body of the message'-
);> > >
Temporary e-mail issue - try again
PL/SQL procedure successfully completed.
</pre>In PPAS:<br />
<pre class="cpp" name="code">CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'PPAS 9.0';
mailhost VARCHAR2(30) := '192.168.23.25'; -- local database host
BEGIN
c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me
utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN OTHERS THEN
IF SMTP_INVALID_OPERATION(SQLERRM) THEN
DBMS_OUTPUT.PUT_LINE('Invalid Operation in Mail attempt using UTL_SMTP.');
ELSIF SMTP_TRANSIENT_ERROR(SQLERRM) THEN
DBMS_OUTPUT.PUT_LINE(' Temporary e-mail issue - try again');
ELSIF SMTP_PERMANENT_ERROR(SQLERRM) THEN
DBMS_OUTPUT.PUT_LINE(' Permanent Error Encountered.');
ELSE
DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE||' Error Message: '||SQLERRM);
END IF;
END;
edb=# exec send_mail(msg_to=>'vibhor.aim@gmail.com',
edb(# msg_subject => 'Hello from PPAS',
edb(# msg_text => 'This is the body of the message');
Temporary e-mail issue - try again
EDB-SPL Procedure successfully completed
edb=#
</pre>Have fun!Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0tag:blogger.com,1999:blog-3301074144512804889.post-88207267445642585042011-12-16T09:36:00.000-08:002011-12-16T09:36:40.007-08:00Postgres Plus Advanced Server 9.1 Beta 1PPAS 9.1 Beta 1 is released. So, I thought to write about it and New Features which are coming.<br />
<br />
Best part of PPAS 9.1 is that it has all features of PostgreSQL 9.1, which I have already discribed in my series of 9.1. Links are given below:<br />
1.<a href="http://vibhorkumar.wordpress.com/2011/12/06/postgresql-9-1-new-in-object-manipulation/"> PostgreSQL Object Manipulation Feature</a>s<br />
2. <a href="http://vibhorkumar.wordpress.com/2011/11/15/new-replication-and-recovery-features-in-postgresql-9-1/">New Replication and Recovery Feature</a>s<br />
3. <a href="http://vibhorkumar.wordpress.com/2011/10/31/queries-improvement-in-postgresql-9-1/">Queries improvement of Core PostgreSQL 9.1</a><br />
4. <a href="http://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/">Updateable Views using INSTEADOF Trigger in PostgreSQL 9.1</a><br />
5.<a href="http://vibhorkumar.wordpress.com/2011/10/27/utility-operations-improvement-in-postgresql-9-1/"> Utility Operation Improvements</a><br />
6. <a href="http://vibhorkumar.wordpress.com/2011/10/26/upsertmerge-using-writable-cte-in-postgresql-9-1/">Upsert/Merge using Writeable CTE</a><br />
7. <a href="http://vibhorkumar.wordpress.com/2011/10/25/new-functionsimprovements-in-postgresql-9-1/">New Functions/Improvement</a><br />
8. <a href="http://vibhorkumar.wordpress.com/2011/10/24/some-object-information-functions-improvements-in-postgresql-9-1/">Object Functions Improvement in PostgreSQL 9.1</a><br />
9.<a href="http://vibhorkumar.wordpress.com/2011/10/24/client-applications-improvements-in-postgresql-9-1/"> Client Application Improvement in PostgreSQL 9.</a>1<br />
<br />
<a href="http://vibhorkumar.wordpress.com/2011/10/20/asynchronoussynchronous-streaming-replication-in-postgresql-9-1/">10. Asynchronous/Synchronous Replication</a><br />
<br />
<br />
There are many other features in PostgreSQL 9.1, which requires some time to write with example. However, below is list of those Improvements:<br />
1. <a href="http://www.depesz.com/index.php/2011/01/03/waiting-for-9-1-unlogged-tables/">Unlogged Tables</a><br />
2. FOREACH IN ARRAY in PL/pgSQL<br />
3. Pl/Perl Improvement:<br />
a. Record type support<br />
b.<a href="http://www.depesz.com/index.php/2011/03/12/waiting-for-9-1-arrays-in-plperl/"> Pl/perl array argument map to Perl Argument.</a><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
c. <a href="http://www.depesz.com/index.php/2011/03/12/waiting-for-9-1-arrays-in-plperl/">Pl/perl Composite-type array to Perl Hash.</a><br />
4. Pl/Python Improvement:<br />
a. Table Support in Pl/Python Function<br />
b. New Validator for Pl/Python<br />
c. SQL Exception handling in Pl/Python Exception Blocks<br />
d. Subtransactions in Pl/Python<br />
e. New Pl/Ptyhon functions for Quoting String (plpy.quote_ident, plpy.quote_literal, and plpy.quote_nullable)<br />
f. Traceback support for Plpython errors<br />
g. Exception Handling in Python3<br />
4. ECPG Improvements:<br />
a. WHERE CURRENT OF in Dynamic Cursor<br />
b. Double digit with percision of 15<br />
5. Libpq Improvements:<br />
a. client_encoding option in Connection<br />
b. PQlibVersion() function for knowing Version of Library<br />
c. PQping and PQpingParams for knowing Server Status.<br />
<br />
With Above Feature PPAS 9.1 has additional Features and Improvement, which users would like to try. List of those Features are given below:<br />
1.VPD (Virtual Private Database) Support<br />
2. Parition Syntax Support :<br />
<pre class="cpp" name="code"> CREATE TABLE employees
(empno numeric(4,0),
ename varchar(10),
job varchar(9),
hiredate timestamp,
)
PARTITION BY RANGE (hiredate)
(PARTITION ten_year_staff
VALUES LESS THAN('01-JAN-2001'),
(PARTITION five_year_staff
VALUES LESS THAN('01-JAN-2006'),
(PARTITION one_year_staff
VALUES LESS THAN('01-JAN-2011'));
CREATE TABLE employees
(empno numeric(4,0),
ename varchar(10),
job varchar(9),
hiredate timestamp,
)
PARTITION BY RANGE (hiredate)
SUBPARTITION BY LIST (job)
(PARTITION ten_year_staff VALUES LESS THAN('01-JAN-2001)
(SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'), SUBPARTITION sales VALUES ('SALESMAN', 'CLERK'), SUBPARTITION support VALUES ('ANALYST')),
(PARTITION five_year_staff VALUES LESS THAN('01-JAN-2006') (SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'),
SUBPARTITION sales VALUES ('SALESMAN', 'CLERK'),
SUBPARTITION support VALUES ('ANALYST')),
(PARTITION one_year_staff VALUES LESS THAN('01-JAN-2011')
(SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'), SUBPARTITION sales VALUES ('SALESMAN', 'CLERK'), SUBPARTITION support VALUES ('ANALYST'));
etc.
</pre>3. HextoRaw and RAWToHEX function support<br />
4. New Hint: ORDERED<br />
5. Ref Cursor as OUT in Procedure and Functions<br />
6. WHEVEVER SQLERROR Syntax Sypport<br />
7. Advanced Server Performance<br />
a. Improvement in Lock Management for Read and Write workloads with more CPUs<br />
b. Index Advisor Support for Composite Indexes<br />
8. New Catalog Views for Partition Table<br />
9. OCI Function: OCIBreak() and OCIReset() for sever connections on Blocking and nonBlocking Mode.<br />
<br />
I would be discussing more about above features in coming Blogs. So, stay tune!Vibhor Kumarhttp://www.blogger.com/profile/12848190692973269508noreply@blogger.com0