Recursive CTE for building menus

[Bob Jones]

Hello,

Whilst researching current thinking on hierarchical queries in
Postgres, I stumbled accross this excellent blog post:

https://illuminatedcomputing.com/posts/2014/09/postgres-cte-for-threaded-comments/

But try as I might, my SQL-foo is not up to scratch to adapt it to my
needs, I keep on loosing child nesting and other weird bug-dom.

My table looks like this :
menu_title text
menu_item_id text
menu_priority integer
menu_parent text

The adaptions I am trying to make are as follows:
- Higher priority moves the item higher up the menu (i.e. adapting
from the original "votes" concept).
- Default alphabetical ordering of titles
- Use of alphanumeric IDs instead of numeric

The only thing that I can get consistently working is the alphanumeric menu IDs.

For menu priorities, postgres does not seem to like mixing numeric and
alphanumeric in an array:
ERROR:  ARRAY types integer and text cannot be matched
LINE 3:  array[-menu_priority,menu_itemid] as path,1 as depth

insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Home','H',1000,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About','A',900,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('FOOBAR','F',800,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Resources','R',NULL,'A');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Background','B',NULL,'A');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Foo','Ff',NULL,'F');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About Bar','Fba',NULL,'Fb');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Team Bar','Fbt',NULL,'Fb');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Bar','Fb',NULL,'F');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Foo World','Ffw',NULL,'Ff');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About World','FFwa',NULL,'Ffw');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('World Introduction','FFwi',1000,'Ffw');

N.B. Although I show NULL as a default priority, I have experimenting
with setting default priorities with no success.

The expected outcome from the above would be (ignore the pretty-print
elements, its just to help human parsing !):
•Home
•About
-> Background
-> Resources
•FOOBAR
-> Bar
->-> About Bar
->-> Team Bar
-> Foo
->-> Foo World
->->-> World Introduction
->->-> About World

Re: pg_basebackup restore a single table

[camarillo]

Thank you!



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Recursive CTE for building menus

[Paul Jungwirth]

On 04/13/2018 02:09 AM, Bob Jones wrote:

The adaptions I am trying to make are as follows:
- Higher priority moves the item higher up the menu (i.e. adapting
from the original "votes" concept).
- Default alphabetical ordering of titles
- Use of alphanumeric IDs instead of numeric


Hi, I wrote that blog post! :-)

This works for me:

WITH RECURSIVE cte (menu_item_id, menu_title, path, menu_parent, depth, 
menu_priority) AS (

  SELECT  menu_item_id,
  menu_title,
  ARRAY[(-menu_priority, menu_title, menu_item_id)] AS path,
  menu_parent,
  1 AS depth,
  menu_priority
  FROMtest_table
  WHERE   menu_parent IS NULL
  UNION ALL
  SELECT  m.menu_item_id,
  m.menu_title,
  cte.path || (-m.menu_priority, m.menu_title, m.menu_item_id),
  m.menu_parent,
  cte.depth + 1,
  m.menu_priority
  FROMtest_table m
  JOIN cte ON m.menu_parent = cte.menu_item_id
)
SELECT  menu_item_id, menu_title, path, depth, menu_priority
FROMcte
ORDER BY path
;
 menu_item_id | menu_title | 
 path | depth | 
menu_priority

--++--+---+---
 H| Home   | {"(-1000,Home,H)"} 
  | 1 | 
 1000
 A| About  | {"(-900,About,A)"} 
  | 1 | 
  900
 B| Background | 
{"(-900,About,A)","(,Background,B)"} 
| 2 |  NULL
 R| Resources  | 
{"(-900,About,A)","(,Resources,R)"} 
| 2 |  NULL
 F| FOOBAR | {"(-800,FOOBAR,F)"} 
  | 1 | 
  800
 Fb   | Bar| {"(-800,FOOBAR,F)","(,Bar,Fb)"} 
  | 2 | 
 NULL
 Fba  | About Bar  | 
{"(-800,FOOBAR,F)","(,Bar,Fb)","(,\"About Bar\",Fba)"} 
| 3 |  NULL
 Fbt  | Team Bar   | 
{"(-800,FOOBAR,F)","(,Bar,Fb)","(,\"Team Bar\",Fbt)"} 
| 3 |  NULL
 Ff   | Foo| {"(-800,FOOBAR,F)","(,Foo,Ff)"} 
  | 2 | 
 NULL
 Ffw  | Foo World  | 
{"(-800,FOOBAR,F)","(,Foo,Ff)","(,\"Foo World\",Ffw)"} 
| 3 |  NULL
 FFwi | World Introduction | 
{"(-800,FOOBAR,F)","(,Foo,Ff)","(,\"Foo World\",Ffw)","(-1000,\"World 
Introduction\",FFwi)"} | 4 |  1000
 FFwa | About World| 
{"(-800,FOOBAR,F)","(,Foo,Ff)","(,\"Foo World\",Ffw)","(,\"About 
World\",FFwa)"} | 4 |  NULL

(12 rows)

So basically the sort is by menu_priority, breaking ties with 
menu_title, then breaking ties with menu_item_id. I think that's what 
you want, right?


The hard part was dealing with mixed types (integer for priority, text 
for the others), because an array has to be all one type. Fortunately 
you can build an array of tuples and the sorting will work as you expect.


I was a little worried to see those tuples appearing like strings in the 
output, but then I remembered that in Postgres ' is a string and " is 
not. Or to prove it:


select * from unnest( array[(1, 'a'::text), (2, 'b'::text)] ) x(a int, b 
text);

 a | b
---+---
 1 | a
 2 | b

Anyway, I hope that gets you what you need!

Yours,


--
Paul  ~{:-)
p...@illuminatedcomputing.com

how to securely delete the storage freed when a table is dropped?

[Jonathan Morgan]

For a system with information stored in a PostgreSQL 9.5 database, in which
data stored in a table that is deleted must be securely deleted (like shred
does to files), and where the system is persistent even though any
particular table likely won't be (so can't just shred the disks at
"completion"), I'm trying to figure out my options for securely deleting
the underlying data files when a table is dropped.

As background, I'm not a DBA, but I am an experienced implementor in many
languages, contexts, and databases. I've looked online and haven't been
able to find a way to ask PostgreSQL to do the equivalent of shredding its
underlying files before releasing them to the OS when a table is DROPped.
Is there a built-in way to ask PostgreSQL to do this? (I might just not
have searched for the right thing - my apologies if I missed something)

A partial answer we're looking at is shredding the underlying data files
for a given relation and its indexes manually before dropping the tables,
but this isn't so elegant, and I'm not sure it is getting all the
information from the tables that we need to delete.

We also are looking at strategies for shredding free space on our data disk
- either running a utility to do that, or periodically replicating the data
volume, swapping in the results of the copy, then shredding the entire
volume that was the source so its "free" space is securely overwritten in
the process.

Are we missing something? Are there other options we haven't found? If we
have to clean up manually, are there other places we need to go to shred
data than the relation files for a given table, and all its related
indexes, in the database's folder? Any help or advice will be greatly
appreciated.

Thanks,

Jonathan Morgan

-- 
"The man with the new idea is a Crank until the idea succeeds."
- Mark Twain, from 'Following the Equator: A Journey Around the World'

Re: Recursive CTE for building menus

[Tim Smith]

On 13 April 2018 at 16:04, Paul Jungwirth  wrote:
> On 04/13/2018 02:09 AM, Bob Jones wrote:
>>
>> The adaptions I am trying to make are as follows:
>> - Higher priority moves the item higher up the menu (i.e. adapting
>> from the original "votes" concept).
>> - Default alphabetical ordering of titles
>> - Use of alphanumeric IDs instead of numeric
>
>
> Hi, I wrote that blog post! :-)
>

Accidentally hit the wrong reply button and sent a reply direct to
Paul instead of list.

I won't repeat my message here, but instead I will just leave a brief
expression of public gratitude to the great man himself for taking the
time to reply.

For the record: A quick simple test indicates (as might be expected)
that the proposed query works.

Thanks again.

Bob

Gratuitous use of savepoint considered silly, if not harmful

[Jerry Sievers]

So I'm (was) puzzled here when a big warehouse system just upgraded to
9.6 which I knew does only a few 100k *real* transactions/day was
wrapping txid_current() so fast, in turn causing some big, nasty tablesl
to age and then require painful long-running vacuums...

Got the brilliant idea to full statement log for just 20 minutes or so
and then do some digging.

OMG my DW team's Pentaho/Kettle driver gizmo emulates single-line
autocommit inserts using savepoints.

It racked up ~1.8M txids meanwhile actually doing only ~900
transactions.

Symptomatic of this also is that your pg_stat_database.(xact_commit +
xact_rollback) counter over time will be wildly smaller then the
advancement of txid_current() perhaps helping in the confusion.

This was csvlogged and field #8 is command-tag which we're pulling out

and summarizing as seen below.

tmp$ grep ^2018 $log-file | grep silly_etl_user | cut -d, -f8 | sort | uniq -c 
| sort -k1,1bnr -k2

1880283 "INSERT"
1879931 "RELEASE"
1879931 "SAVEPOINT"
 314838 "SELECT"
 314298 "UPDATE"
   2681 "idle"
   2677 "authentication"
   1967 "SET"
898 "COMMIT"
897 "BEGIN"
160 "DELETE"
 83 "TRUNCATE TABLE"
  6 "DROP TABLE"
  2 "CREATE INDEX"
  2 "CREATE TABLE AS"

Anyway, I felt this was worth sharing :-)

Thx

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800

Re: Gratuitous use of savepoint considered silly, if not harmful

[Melvin Davidson]

On Fri, Apr 13, 2018 at 2:32 PM, Jerry Sievers 
wrote:

> So I'm (was) puzzled here when a big warehouse system just upgraded to
> 9.6 which I knew does only a few 100k *real* transactions/day was
> wrapping txid_current() so fast, in turn causing some big, nasty tablesl
> to age and then require painful long-running vacuums...
>
> Got the brilliant idea to full statement log for just 20 minutes or so
> and then do some digging.
>
> OMG my DW team's Pentaho/Kettle driver gizmo emulates single-line
> autocommit inserts using savepoints.
>
> It racked up ~1.8M txids meanwhile actually doing only ~900
> transactions.
>
> Symptomatic of this also is that your pg_stat_database.(xact_commit +
> xact_rollback) counter over time will be wildly smaller then the
> advancement of txid_current() perhaps helping in the confusion.
>
> This was csvlogged and field #8 is command-tag which we're pulling out
>
> and summarizing as seen below.
>
> tmp$ grep ^2018 $log-file | grep silly_etl_user | cut -d, -f8 | sort |
> uniq -c | sort -k1,1bnr -k2
>
> 1880283 "INSERT"
> 1879931 "RELEASE"
> 1879931 "SAVEPOINT"
>  314838 "SELECT"
>  314298 "UPDATE"
>2681 "idle"
>2677 "authentication"
>1967 "SET"
> 898 "COMMIT"
> 897 "BEGIN"
> 160 "DELETE"
>  83 "TRUNCATE TABLE"
>   6 "DROP TABLE"
>   2 "CREATE INDEX"
>   2 "CREATE TABLE AS"
>
> Anyway, I felt this was worth sharing :-)
>
> Thx
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
> p: 312.241.7800
>
>
*As Forest Gump once said "Stupid is as stupid does". *😁


-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

hardcode password in connect string

[David Gauthier]

Hi:

PG v9.5.2 on RHEL

I like to use an alias to connect to my favorite DBs but don't like to
enter passwords.  I used to just disable passwords (working behind a
firewall), but this one is different.  I see nothing in the interactive
connect string where I can enter the password...

psql -h thehost -U memyselfi mydb

Missing something like "-p mypassword"

Is there a way I can stick the pw in the linux alias definition ?

Just thinking something like this may be available since hardcoded
passwords are supported in perl/dbi, pg driver

Thanks !

Re: hardcode password in connect string

[James Keener]

Is setting it as an environment variable an option? 
https://www.postgresql.org/docs/9.1/static/libpq-envars.html

Alternatively, a service file? 
https://www.postgresql.org/docs/9.0/static/libpq-pgservice.html

Jim

On April 13, 2018 2:43:01 PM EDT, David Gauthier  
wrote:
>Hi:
>
>PG v9.5.2 on RHEL
>
>I like to use an alias to connect to my favorite DBs but don't like to
>enter passwords.  I used to just disable passwords (working behind a
>firewall), but this one is different.  I see nothing in the interactive
>connect string where I can enter the password...
>
>psql -h thehost -U memyselfi mydb
>
>Missing something like "-p mypassword"
>
>Is there a way I can stick the pw in the linux alias definition ?
>
>Just thinking something like this may be available since hardcoded
>passwords are supported in perl/dbi, pg driver
>
>Thanks !

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: hardcode password in connect string

[Rob Sargent]

On 04/13/2018 12:46 PM, James Keener wrote:
Is setting it as an environment variable an option? 
https://www.postgresql.org/docs/9.1/static/libpq-envars.html


Alternatively, a service file? 
https://www.postgresql.org/docs/9.0/static/libpq-pgservice.html


Jim

On April 13, 2018 2:43:01 PM EDT, David Gauthier 
 wrote:


Hi:

PG v9.5.2 on RHEL

I like to use an alias to connect to my favorite DBs but don't
like to enter passwords.  I used to just disable passwords
(working behind a firewall), but this one is different.  I see
nothing in the interactive connect string where I can enter the
password...

psql -h thehost -U memyselfi mydb

Missing something like "-p mypassword"

Is there a way I can stick the pw in the linux alias definition ?

Just thinking something like this may be available since hardcoded
passwords are supported in perl/dbi, pg driver

Thanks !


--
Sent from my Android device with K-9 Mail. Please excuse my brevity. 

This is usually done in ~/.pgpass

Re: hardcode password in connect string

[David Gauthier]

PGPASSWORD env var works fine.
Thanks !


On Fri, Apr 13, 2018 at 2:46 PM, James Keener  wrote:

> Is setting it as an environment variable an option?
> https://www.postgresql.org/docs/9.1/static/libpq-envars.html
>
> Alternatively, a service file? https://www.postgresql.org/
> docs/9.0/static/libpq-pgservice.html
>
> Jim
>
>
> On April 13, 2018 2:43:01 PM EDT, David Gauthier 
> wrote:
>>
>> Hi:
>>
>> PG v9.5.2 on RHEL
>>
>> I like to use an alias to connect to my favorite DBs but don't like to
>> enter passwords.  I used to just disable passwords (working behind a
>> firewall), but this one is different.  I see nothing in the interactive
>> connect string where I can enter the password...
>>
>> psql -h thehost -U memyselfi mydb
>>
>> Missing something like "-p mypassword"
>>
>> Is there a way I can stick the pw in the linux alias definition ?
>>
>> Just thinking something like this may be available since hardcoded
>> passwords are supported in perl/dbi, pg driver
>>
>> Thanks !
>>
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>

Re: how to securely delete the storage freed when a table is dropped?

[Ron]

On 04/13/2018 12:48 PM, Jonathan Morgan wrote:
For a system with information stored in a PostgreSQL 9.5 database, in 
which data stored in a table that is deleted must be securely deleted 
(like shred does to files), and where the system is persistent even though 
any particular table likely won't be (so can't just shred the disks at 
"completion"), I'm trying to figure out my options for securely deleting 
the underlying data files when a table is dropped.


As background, I'm not a DBA, but I am an experienced implementor in many 
languages, contexts, and databases. I've looked online and haven't been 
able to find a way to ask PostgreSQL to do the equivalent of shredding its 
underlying files before releasing them to the OS when a table is DROPped. 
Is there a built-in way to ask PostgreSQL to do this? (I might just not 
have searched for the right thing - my apologies if I missed something)


A partial answer we're looking at is shredding the underlying data files 
for a given relation and its indexes manually before dropping the tables, 
but this isn't so elegant, and I'm not sure it is getting all the 
information from the tables that we need to delete.


We also are looking at strategies for shredding free space on our data 
disk - either running a utility to do that, or periodically replicating 
the data volume, swapping in the results of the copy, then shredding the 
entire volume that was the source so its "free" space is securely 
overwritten in the process.


Are we missing something? Are there other options we haven't found? If we 
have to clean up manually, are there other places we need to go to shred 
data than the relation files for a given table, and all its related 
indexes, in the database's folder? Any help or advice will be greatly 
appreciated.


I'd write a program that fills all free space on disk with a specific 
pattern.  You're probably using a logging filesystem, so that'll be far from 
perfect, though.


--
Angular momentum makes the world go 'round.

Re: how to securely delete the storage freed when a table is dropped?

[Steve Atkins]

> On Apr 13, 2018, at 10:48 AM, Jonathan Morgan  
> wrote:
> 
> For a system with information stored in a PostgreSQL 9.5 database, in which 
> data stored in a table that is deleted must be securely deleted (like shred 
> does to files), and where the system is persistent even though any particular 
> table likely won't be (so can't just shred the disks at "completion"), I'm 
> trying to figure out my options for securely deleting the underlying data 
> files when a table is dropped.
> 
> As background, I'm not a DBA, but I am an experienced implementor in many 
> languages, contexts, and databases. I've looked online and haven't been able 
> to find a way to ask PostgreSQL to do the equivalent of shredding its 
> underlying files before releasing them to the OS when a table is DROPped. Is 
> there a built-in way to ask PostgreSQL to do this? (I might just not have 
> searched for the right thing - my apologies if I missed something)
> 
> A partial answer we're looking at is shredding the underlying data files for 
> a given relation and its indexes manually before dropping the tables, but 
> this isn't so elegant, and I'm not sure it is getting all the information 
> from the tables that we need to delete.
> 
> We also are looking at strategies for shredding free space on our data disk - 
> either running a utility to do that, or periodically replicating the data 
> volume, swapping in the results of the copy, then shredding the entire volume 
> that was the source so its "free" space is securely overwritten in the 
> process.
> 
> Are we missing something? Are there other options we haven't found? If we 
> have to clean up manually, are there other places we need to go to shred data 
> than the relation files for a given table, and all its related indexes, in 
> the database's folder? Any help or advice will be greatly appreciated.

Just "securely" deleting the files won't help much, as you'll leave data in 
spare space on the filesystem, in filesystem journals and so on.

Maybe put the transient tables an indexes in their own tablespace on their own 
filesystem, periodically move them to another tablespace and wipe the first 
one's filesystem (either physically or forgetting the key for an encrypted FS)? 
That'd leave you with just the WAL data to deal with.

Seems like a slightly odd requirement, though. What's your threat model?

Cheers,
  Steve

Re: how to securely delete the storage freed when a table is dropped?

[Ozz Nixon]

There are free utilities that do government leave wipes. The process would be, 
drop the table, shrink the old table space then (if linux based), dd fill the 
drive, and use wipe, 5x or 8x deletion to make sure the drive does not have 
readable imprints on the platers.

Now what Jonathan mentions - sounds like he wants to do the same to the 
physical table. Never dabbling into PSQL’s storage and optimization algorithms, 
I would first assume, a script to do a row by row update table set 
field1…fieldx, different data patterns, existing field value length and field 
max length. Run the script at least 5 to 8 times, then drop the table .. the 
problem will be, does PSQL use a new page as you do this, then you are just 
playing with yourself. Let alone, how does PSQL handle indexes - new pages, or 
overwrite the existing page? And is any NPI (Non-Public-Info) data in the index 
itself?

   * So any PSQL core-engine guys reading?

O.

> On Apr 13, 2018, at 3:03 PM, Ron  wrote:
> 
> 
> 
> On 04/13/2018 12:48 PM, Jonathan Morgan wrote:
>> For a system with information stored in a PostgreSQL 9.5 database, in which 
>> data stored in a table that is deleted must be securely deleted (like shred 
>> does to files), and where the system is persistent even though any 
>> particular table likely won't be (so can't just shred the disks at 
>> "completion"), I'm trying to figure out my options for securely deleting the 
>> underlying data files when a table is dropped.
>> 
>> As background, I'm not a DBA, but I am an experienced implementor in many 
>> languages, contexts, and databases. I've looked online and haven't been able 
>> to find a way to ask PostgreSQL to do the equivalent of shredding its 
>> underlying files before releasing them to the OS when a table is DROPped. Is 
>> there a built-in way to ask PostgreSQL to do this? (I might just not have 
>> searched for the right thing - my apologies if I missed something)
>> 
>> A partial answer we're looking at is shredding the underlying data files for 
>> a given relation and its indexes manually before dropping the tables, but 
>> this isn't so elegant, and I'm not sure it is getting all the information 
>> from the tables that we need to delete.
>> 
>> We also are looking at strategies for shredding free space on our data disk 
>> - either running a utility to do that, or periodically replicating the data 
>> volume, swapping in the results of the copy, then shredding the entire 
>> volume that was the source so its "free" space is securely overwritten in 
>> the process.
>> 
>> Are we missing something? Are there other options we haven't found? If we 
>> have to clean up manually, are there other places we need to go to shred 
>> data than the relation files for a given table, and all its related indexes, 
>> in the database's folder? Any help or advice will be greatly appreciated.
> 
> I'd write a program that fills all free space on disk with a specific 
> pattern.  You're probably using a logging filesystem, so that'll be far from 
> perfect, though.
> 
> -- 
> Angular momentum makes the world go 'round.
> 

Re: how to securely delete the storage freed when a table is dropped?

[Ron]

After you drop a table, aren't the associated files dropped?

On 04/13/2018 02:29 PM, Ozz Nixon wrote:

There are free utilities that do government leave wipes. The process would be, 
drop the table, shrink the old table space then (if linux based), dd fill the 
drive, and use wipe, 5x or 8x deletion to make sure the drive does not have 
readable imprints on the platers.

Now what Jonathan mentions - sounds like he wants to do the same to the 
physical table. Never dabbling into PSQL’s storage and optimization algorithms, 
I would first assume, a script to do a row by row update table set 
field1…fieldx, different data patterns, existing field value length and field 
max length. Run the script at least 5 to 8 times, then drop the table .. the 
problem will be, does PSQL use a new page as you do this, then you are just 
playing with yourself. Let alone, how does PSQL handle indexes - new pages, or 
overwrite the existing page? And is any NPI (Non-Public-Info) data in the index 
itself?

* So any PSQL core-engine guys reading?

O.


On Apr 13, 2018, at 3:03 PM, Ron  wrote:



On 04/13/2018 12:48 PM, Jonathan Morgan wrote:

For a system with information stored in a PostgreSQL 9.5 database, in which data stored 
in a table that is deleted must be securely deleted (like shred does to files), and where 
the system is persistent even though any particular table likely won't be (so can't just 
shred the disks at "completion"), I'm trying to figure out my options for 
securely deleting the underlying data files when a table is dropped.

As background, I'm not a DBA, but I am an experienced implementor in many 
languages, contexts, and databases. I've looked online and haven't been able to 
find a way to ask PostgreSQL to do the equivalent of shredding its underlying 
files before releasing them to the OS when a table is DROPped. Is there a 
built-in way to ask PostgreSQL to do this? (I might just not have searched for 
the right thing - my apologies if I missed something)

A partial answer we're looking at is shredding the underlying data files for a 
given relation and its indexes manually before dropping the tables, but this 
isn't so elegant, and I'm not sure it is getting all the information from the 
tables that we need to delete.

We also are looking at strategies for shredding free space on our data disk - either 
running a utility to do that, or periodically replicating the data volume, swapping in 
the results of the copy, then shredding the entire volume that was the source so its 
"free" space is securely overwritten in the process.

Are we missing something? Are there other options we haven't found? If we have 
to clean up manually, are there other places we need to go to shred data than 
the relation files for a given table, and all its related indexes, in the 
database's folder? Any help or advice will be greatly appreciated.

I'd write a program that fills all free space on disk with a specific pattern.  
You're probably using a logging filesystem, so that'll be far from perfect, 
though.

--
Angular momentum makes the world go 'round.



--
Angular momentum makes the world go 'round.

Re: how to securely delete the storage freed when a table is dropped?

[Nick Cleaton]

On 13 April 2018 at 18:48, Jonathan Morgan
 wrote:
> For a system with information stored in a PostgreSQL 9.5 database, in which
> data stored in a table that is deleted must be securely deleted (like shred
> does to files), and where the system is persistent even though any
> particular table likely won't be (so can't just shred the disks at
> "completion"), I'm trying to figure out my options for securely deleting the
> underlying data files when a table is dropped.
>
> As background, I'm not a DBA, but I am an experienced implementor in many
> languages, contexts, and databases. I've looked online and haven't been able
> to find a way to ask PostgreSQL to do the equivalent of shredding its
> underlying files before releasing them to the OS when a table is DROPped. Is
> there a built-in way to ask PostgreSQL to do this? (I might just not have
> searched for the right thing - my apologies if I missed something)
>
> A partial answer we're looking at is shredding the underlying data files for
> a given relation and its indexes manually before dropping the tables, but
> this isn't so elegant, and I'm not sure it is getting all the information
> from the tables that we need to delete.
>
> We also are looking at strategies for shredding free space on our data disk
> - either running a utility to do that, or periodically replicating the data
> volume, swapping in the results of the copy, then shredding the entire
> volume that was the source so its "free" space is securely overwritten in
> the process.
>
> Are we missing something? Are there other options we haven't found? If we
> have to clean up manually, are there other places we need to go to shred
> data than the relation files for a given table, and all its related indexes,
> in the database's folder? Any help or advice will be greatly appreciated.

Can you encrypt the data in the application, above the DB level ? That
would be cleaner if you can.

If not, you'll have to worry about both the DB's data files themselves
and the WAL files in pg_xlog/ which hold copies of the recently
written data. Even if you securely scrub the deleted parts of the
filesystems after dropping the table, there could still be copies of
secret table data in WAL files that haven't yet been overwritten.

One way to scrub deleted files would be to use ZFS and have an extra
disk. When it's time to scrub, "zpool attach" the extra disk to your
zpool, which will cause ZFS to copy over only the files that haven't
been deleted, in the background. When that's finished you can detach
the original disk from the zpool and then do a low-level overwrite of
that entire disk. For extra security points use encrypted block
devices underneath ZFS, and instead of scrubbing the disk just destroy
the encryption key that you were using for it.

Re: Table schema inhancement

[Adrian Klaver]

On 04/12/2018 05:59 PM, hmidi slim wrote:

Hi,
I have these tables:
price_per_occupation: id (integer), product_price_period_id(integer), 
occupation_type(integer), price (numeric)


product_price_period; id(integer), product_id(integer), 
is_monday(boolean), is_tuesday(boolean), is_wednesday(boolean), 
is_thursday(boolean), is_friday(boolean), is_saturday(boolean), 
is_sunday(boolean), price_period(daterange)


occupation_type: id(integer), name(varchar)

product: id(integer), name(varchar)

I run this query:
/*select price_per_occupation.price, product_price_period.price_period, 
occupation_type.name 

*/
/*from price_per_occupation inner join product_price_period on 
product_price_period.id = 
price_per_occupation.product_price_period_id

*/
/*inner join occupation_type on occupation_type.id 
 = price_per_occupation.occupation_type

*/
/*inner join product on product.id  = 
product_price_period.product_id

*/
/*where product_price_period.price_period @> '[2018-07-22, 2018-07-23]'
*/
/*and occupation_type.id  = 1*/


This query returns all the products with an occupation_type = 1 and have 
the period_price between 2018-07-22 and 2018-07-23.


However I need to verify if the boolean values verified.
E.g if is_monday = true the date corresponding to Monday will be 
eliminated from the period.
if is_sunday = true and is_friday = true the dates corresponding to 
Sunday and Friday will be eliminated from the period interval.
If I will choose all the products in the interval 
[2018-04-07,2018-04-14] and is_monday = true and is_thursday= true

the date of monday is 09/04/2018 and date of friday is 13/04/2018.
I have to get all products contained in [2018-04-07,2018-04-08] U 
[2018-04-10, 2018-04-12] U [2018-04-14, 2018-04-14]


In order to get the date of truthy columns I should execute a function 
which contains a query like that:


|select *
|
|from generate_series(date '2018-04-07', date '2018-04-14', interval '1 
day') the day

|
|where extract ('dow', the_day) = 1


|
|I'm face many problems with this schema;
|
|I should verify the boolean values.
|
|I should extract the corresponding dates based to the values.
|
|I decompose the period into different periods then run the query to 
fetch the products.


|
|How can I enhance the query? or is there any proposition for the schema 
table to enhance it?


My 2 cents eliminate the is_* fields and create a single field:

dow_verified integer[]

Then assuming non-iso week day number Sunday(0) --> Saturday(6) in the 
array enter the day of week(dow) numbers for verified days e.g.:


ARRAY[0, 1, 4]

for Sunday, Monday, Thursday.

Then to get the days that are not verified over a period something like:

WITH dp AS (
SELECT
extract('dow' FROM generate_series('04/01/18'::date,
   '04/14/18'::date, '1 day'))
AS dow,
generate_series('04/01/18'::date, '04/14/18'::date, '1 day')
AS dt
)
SELECT
dp.*
FROM
dp
WHERE
dp.dow NOT IN (
(
SELECT
*
FROM
unnest(ARRAY [ 0, 1, 4 ]) AS dow_dt)
)

ORDER BY
dt;

dow |   dt
-+
   2 | 2018-04-03 00:00:00-07
   3 | 2018-04-04 00:00:00-07
   5 | 2018-04-06 00:00:00-07
   6 | 2018-04-07 00:00:00-07
   2 | 2018-04-10 00:00:00-07
   3 | 2018-04-11 00:00:00-07
   5 | 2018-04-13 00:00:00-07
   6 | 2018-04-14 00:00:00-07
(8 rows)




|
||
||






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Barman versus pgBackRest

[David Steele]

Hi Thomas,

On 4/11/18 3:14 AM, Thomas Poty wrote:


Sorry for answering only now but I just saw you answer only now.

 > To be clear, I'm the original author and primary maintainer of
pgBackRest.

I am very happy to see guys like you to take time to answer me. Thank you


You are welcome.  Users are the reason I work on this project.


 >This a good feature, and one that has been requested for pgBackRest. You
 >can do this fairly trivially with ssh, however, so it generally hasn't
 >been a big deal for people.  Is there a particular reason you need this
 >feature?

The reason is probably a psychologic matter but I like the idea of a 
unique connecting point to restore DBs of different location.


I am very impatient to see "replication slot" support and "remote 
restore" feature added.


Remote restore is likely to land first, though neither feature is 
currently at the top of the list.  Unfortunately, we have limited 
resources and must prioritize.


Regards,
--
-David
da...@pgmasters.net