ZFS filesystem - supported ?

[Laura Smith]

Hi,

Given an upcoming server upgrade, I'm contemplating moving away from XFS to ZFS 
(specifically the ZoL flavour via Debian 11). BTRFS seems to be falling away 
(e.g. with Redhat deprecating it etc.), hence my preference for ZFS.

However, somewhere in the back of my mind I seem to have a recollection of 
reading about what could be described as a "strong encouragement" to stick with 
more traditional options such as ext4 or xfs.

A brief search of the docs for "xfs" didn't come up with anything, hence the 
question here.

Thanks !

Laura

Re: WAL streaming and dropping a large table

[Mladen Gogala]

On 10/21/21 16:23, Rory Falloon wrote:

Hi,

My postgres server setup is thus: a production database, which is 
using WAL streaming (hot standby) to four other hosts of various 
latencies. I Have a table that is ~200GB which has been backed up, and 
now I Want to remove it. The table is not in use, it is a child table 
to a parent table that _is_ in use but I foresee no issues here. The 
total DB size is roughly 300GB.  The main reason for needing to remove 
it is to recover the space back on the partition which is humming away 
at 88% usage, and the table I wish to drop is better off in an archive 
somewhere.


I just removed around 10% of it with  'delete from', which of course 
cascaded to the replication hosts. This increased the size of my 
pg_xlog folder (from ~3GB to ~6.5GB) which of course increased my 
partition usage. Obviously this is expected, but I wonder what would 
happen if I had issued the 'drop table'?


I expect the nature of enabling max_replication_slots would mean the 
database would retain the wal segments until all have caught up; it 
could take quite a long time to 'catch up' after the 'drop table' 
command? It took about 10 minutes before the pg_xlog folder size had 
'settled down' to what I normally see as default which is ~3GB.


> wal_keep_segments is defined as 128,
> wal_max_size is not defined,
> max_replication_slots is enabled

I'd prefer to use 'drop table' as it would recover the data 
immediately, but not if it will impact the production database in a 
way that the partition will become full, which defeats the purpose. Is 
it advisable to move the pg_xlog folder to another volume on the 
system with more space (which I have) and symlink - obviously a stop & 
start of the db required - and then let the WAL archives fill up as 
need be? Or am I missing something obvious (likely)


Thanks

Well, the difference between "drop table" and "delete" is that "drop 
table" is transaction on the data dictionary. PostgreSQL has 
transactional DDL, which means that "drop table" can be rolled back, so 
that transaction has to be protected by WAL, but it is just the log of 
the dictionary tables. There is the 3rd option, called "truncate" which 
is also a DDL which creates a new empty table with the same description 
and discards the old files.  Both 'drop" and "truncate" will not 
generate much WAL logs. On the other hand, "delete" is a regular DML 
transaction which will generate logs needed to recover the 200GB table.




--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: ZFS filesystem - supported ?

[Mladen Gogala]

On 10/23/21 07:29, Laura Smith wrote:

Hi,

Given an upcoming server upgrade, I'm contemplating moving away from XFS to ZFS 
(specifically the ZoL flavour via Debian 11). BTRFS seems to be falling away 
(e.g. with Redhat deprecating it etc.), hence my preference for ZFS.

However, somewhere in the back of my mind I seem to have a recollection of reading about 
what could be described as a "strong encouragement" to stick with more 
traditional options such as ext4 or xfs.

A brief search of the docs for "xfs" didn't come up with anything, hence the 
question here.

Thanks !

Laura



Hi Laura,

May I ask why would you like to change file systems? Probably because of 
the snapshot capability? However, ZFS performance leaves much to be 
desired. Please see the following article:


https://www.phoronix.com/scan.php?page=article&item=ubuntu1910-ext4-zfs&num=1

This is relatively new, from 2019. On the page 3 there are tests with 
SQLite, Cassandra and RocksDB. Ext4 is much faster in all of them. 
Finally, there is another article about relational databases and ZFS:


https://blog.docbert.org/oracle-on-zfs/

In other words, I would test very thoroughly because your performance is 
likely to suffer. As for the supported part, that's not a problem. 
Postgres supports all modern file systems. It uses Posix system calls to 
manipulate, read and write files. Furthermore, if you need snapshots, 
disk arrays like NetApp, Hitachi or EMC can always provide that.


Regards



--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: ZFS filesystem - supported ?

[Laura Smith]

On Saturday, October 23rd, 2021 at 14:03, Mladen Gogala 
 wrote:

> On 10/23/21 07:29, Laura Smith wrote:
>
> > Hi,
> >
> > Given an upcoming server upgrade, I'm contemplating moving away from XFS to 
> > ZFS (specifically the ZoL flavour via Debian 11). BTRFS seems to be falling 
> > away (e.g. with Redhat deprecating it etc.), hence my preference for ZFS.
> >
> > However, somewhere in the back of my mind I seem to have a recollection of 
> > reading about what could be described as a "strong encouragement" to stick 
> > with more traditional options such as ext4 or xfs.
> >
> > A brief search of the docs for "xfs" didn't come up with anything, hence 
> > the question here.
> >
> > Thanks !
> >
> > Laura
>
> Hi Laura,
>
> May I ask why would you like to change file systems? Probably because of
>
> the snapshot capability? However, ZFS performance leaves much to be
>
> desired. Please see the following article:
>
> https://www.phoronix.com/scan.php?page=article&item=ubuntu1910-ext4-zfs&num=1
>
> This is relatively new, from 2019. On the page 3 there are tests with
>
> SQLite, Cassandra and RocksDB. Ext4 is much faster in all of them.
>
> Finally, there is another article about relational databases and ZFS:
>
> https://blog.docbert.org/oracle-on-zfs/
>
> In other words, I would test very thoroughly because your performance is
>
> likely to suffer. As for the supported part, that's not a problem.
>
> Postgres supports all modern file systems. It uses Posix system calls to
>
> manipulate, read and write files. Furthermore, if you need snapshots,
>
> disk arrays like NetApp, Hitachi or EMC can always provide that.
>
> Regards
>
>
> --
>
> Mladen Gogala
>
> Database Consultant
>
> Tel: (347) 321-1217
>
> https://dbwhisperer.wordpress.com

Hi Mladen,

Yes indeed, snapshots is the primary reason, closely followed by 
zfssend/receive.

I'm no stranger to using LVM snapshots with ext4/xfs but it requires a custom 
shell script to manage the whole process around backups.  I feel the whole 
thing could well be a lot cleaner with zfs.

Thank you for the links, I will take a look.

Laura

Re: Issue with pg_basebackup v.11

[Tom Lane]

Ninad Shah  writes:
> Would keepalive setting address and mitigate the issue?

[ shrug... ]  Maybe; nobody else has more information about this
situation than you do.  I suggested something to experiment with.

regards, tom lane

Re: ZFS filesystem - supported ?

[Mladen Gogala]

On 10/23/21 09:37, Laura Smith wrote:

Hi Mladen,

Yes indeed, snapshots is the primary reason, closely followed by 
zfssend/receive.

I'm no stranger to using LVM snapshots with ext4/xfs but it requires a custom 
shell script to manage the whole process around backups.  I feel the whole 
thing could well be a lot cleaner with zfs.

Thank you for the links, I will take a look.

Laura


Yes, ZFS is extremely convenient. It's a volume manager and a file 
system, all rolled into one, with some additiional convenient tools. 
However, performance is a major concern. If your application is OLTP, 
ZFS might be a tad too slow for your performance requirements. On the 
other hand, snapshots can save you  a lot of time with backups, 
especially if you have some commercial backup capable of multiple 
readers. If your application is OLTP, ZFS might be a tad too slow for 
your performance requirements. The only way to find out is to test. The 
ideal tool for testing is pgio:


https://kevinclosson.net/2019/09/21/announcing-pgio-the-slob-method-for-postgresql-is-released-under-apache-2-0-and-available-at-github/

For those who do not know, Kevin Closson was the technical architect who 
has built both Exadata and EMC XTRemIO. He is now the principal engineer 
of the Amazon RDS. This part is intended only for those who would tell 
him that "Oracle has it is not good enough" if he ever decided to post here.



--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Python3 for PostgreSQL 14

[Дмитрий Иванов]

Да.
Python extension issues occurred on Windows Server 2012 and Windows 10 Pro.
Experimentally, when installing version 3.7.4, everything worked. This is
my first time deploying Debian, and since I didn't want to deploy multiple
versions, I asked a question. I have installed Python 3.10 x64. when faced
with a lack of information on installing an extension pack, it would be a
good idea to highlight this in the help.
https://debian.pkgs.org/11/postgresql-amd64/postgresql-plpython3-14_14.0-1.pgdg110+1_amd64.deb.html
:

# sudo apt-get install postgresql-plpython3-14

After that, I have successfully installed the extension and executed the
audit function

сб, 23 окт. 2021 г. в 09:04, Adrian Klaver :

> On 10/22/21 19:58, Дмитрий Иванов wrote:
> > Good afternoon. Tell me what version of Python3 PostgreSQL 14 is
> > targeting. I had problems with pairing version 12, working on 3.7.4
>
> 1) Where did you install Postgres from?
>
> 2) Define problems.
>
> 3) To be clear you are trying to use plpython3u, correct?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Python3 for PostgreSQL 14

[Adrian Klaver]

On 10/23/21 13:00, Дмитрий Иванов wrote:

Да.
Python extension issues occurred on Windows Server 2012 and Windows 10 
Pro. Experimentally, when installing version 3.7.4, everything worked. 


If you used the EDB installer for the Windows install of Postgres then 
you need to use their Language Pack to match up with what the plpython 
expects.


This is my first time deploying Debian, and since I didn't want to 
deploy multiple versions, I asked a question. I have installed Python 
3.10 x64. when faced with a lack of information on installing an 
extension pack, it would be a good idea to highlight this in the help. 
https://debian.pkgs.org/11/postgresql-amd64/postgresql-plpython3-14_14.0-1.pgdg110+1_amd64.deb.html 
:


This is the Debian project packages, not something the Postgres project 
controls.


The project does control this:

https://www.postgresql.org/download/linux/debian/

Which has the section

"Included in distribution"


that points out you may need to install additional packages.

This is also pointed out here:

https://www.postgresql.org/docs/current/plpython.html

"
Note

Users of source packages must specially enable the build of PL/Python 
during the installation process. (Refer to the installation instructions 
for more information.) Users of binary packages might find PL/Python in 
a separate subpackage.

"


After that, I have successfully installed the extension and executed the 
audit function


Does this mean everything is working now?



сб, 23 окт. 2021 г. в 09:04, Adrian Klaver >:


On 10/22/21 19:58, Дмитрий Иванов wrote:
 > Good afternoon. Tell me what version of Python3 PostgreSQL 14 is
 > targeting. I had problems with pairing version 12, working on 3.7.4

1) Where did you install Postgres from?

2) Define problems.

3) To be clear you are trying to use plpython3u, correct?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Python3 for PostgreSQL 14

[Дмитрий Иванов]

Yes, it does.
>If you used the EDB installer for the Windows install of Postgres then
>you need to use their Language Pack to match up with what the plpython
>expects.
It was obvious, but it didn't work. This is not a problem at the moment.

>This is the Debian project packages, not something the Postgres project
>controls.
>The project does control this:
>https://www.postgresql.org/download/linux/debian/
You may be right, but it didn't help me find the package
postgresql-plpython3-14.
Yes everything works, thanks!

вс, 24 окт. 2021 г. в 01:40, Adrian Klaver :

> On 10/23/21 13:00, Дмитрий Иванов wrote:
> > Да.
> > Python extension issues occurred on Windows Server 2012 and Windows 10
> > Pro. Experimentally, when installing version 3.7.4, everything worked.
>
> If you used the EDB installer for the Windows install of Postgres then
> you need to use their Language Pack to match up with what the plpython
> expects.
>
> > This is my first time deploying Debian, and since I didn't want to
> > deploy multiple versions, I asked a question. I have installed Python
> > 3.10 x64. when faced with a lack of information on installing an
> > extension pack, it would be a good idea to highlight this in the help.
> >
> https://debian.pkgs.org/11/postgresql-amd64/postgresql-plpython3-14_14.0-1.pgdg110+1_amd64.deb.html
> > <
> https://debian.pkgs.org/11/postgresql-amd64/postgresql-plpython3-14_14.0-1.pgdg110+1_amd64.deb.html
> >:
>
> This is the Debian project packages, not something the Postgres project
> controls.
>
> The project does control this:
>
> https://www.postgresql.org/download/linux/debian/
>
> Which has the section
>
> "Included in distribution"
>
>
> that points out you may need to install additional packages.
>
> This is also pointed out here:
>
> https://www.postgresql.org/docs/current/plpython.html
>
> "
> Note
>
> Users of source packages must specially enable the build of PL/Python
> during the installation process. (Refer to the installation instructions
> for more information.) Users of binary packages might find PL/Python in
> a separate subpackage.
> "
>
>
> > After that, I have successfully installed the extension and executed the
> > audit function
>
> Does this mean everything is working now?
>
> >
> > сб, 23 окт. 2021 г. в 09:04, Adrian Klaver  > >:
> >
> > On 10/22/21 19:58, Дмитрий Иванов wrote:
> >  > Good afternoon. Tell me what version of Python3 PostgreSQL 14 is
> >  > targeting. I had problems with pairing version 12, working on
> 3.7.4
> >
> > 1) Where did you install Postgres from?
> >
> > 2) Define problems.
> >
> > 3) To be clear you are trying to use plpython3u, correct?
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: ZFS filesystem - supported ?

[Lucas]

On 2021-10-24 06:48, Mladen Gogala wrote:


On 10/23/21 09:37, Laura Smith wrote:


Hi Mladen,

Yes indeed, snapshots is the primary reason, closely followed by 
zfssend/receive.


I'm no stranger to using LVM snapshots with ext4/xfs but it requires a 
custom shell script to manage the whole process around backups.  I 
feel the whole thing could well be a lot cleaner with zfs.


Thank you for the links, I will take a look.

Laura


Yes, ZFS is extremely convenient. It's a volume manager and a file 
system, all rolled into one, with some additiional convenient tools. 
However, performance is a major concern. If your application is OLTP, 
ZFS might be a tad too slow for your performance requirements. On the 
other hand, snapshots can save you  a lot of time with backups, 
especially if you have some commercial backup capable of multiple 
readers. If your application is OLTP, ZFS might be a tad too slow for 
your performance requirements. The only way to find out is to test. The 
ideal tool for testing is pgio:


https://kevinclosson.net/2019/09/21/announcing-pgio-the-slob-method-for-postgresql-is-released-under-apache-2-0-and-available-at-github/

For those who do not know, Kevin Closson was the technical architect 
who has built both Exadata and EMC XTRemIO. He is now the principal 
engineer of the Amazon RDS. This part is intended only for those who 
would tell him that "Oracle has it is not good enough" if he ever 
decided to post here.


Interesting subject... I'm working on a migration from PG 9.2 to PG 14 
and was wondering which File System should I use. Looking at this 
thread, looks like I should keep using ext4.


I don't know where you have your database deployed, but in my case is in 
AWS EC2 instances. The way I handle backups is at the block storage 
level, performing EBS snapshots.


This has proven to work very well for me. I had to restore a few backups 
already and it always worked. The bad part is that I need to stop the 
database before performing the Snapshot, for data integrity, so that 
means that I have a hot-standby server only for these snapshots.


Lucas

Re: ZFS filesystem - supported ?

[Mladen Gogala]

On 10/23/21 23:12, Lucas wrote:
I don't know where you have your database deployed, but in my case is 
in AWS EC2 instances. The way I handle backups is at the block storage 
level, performing EBS snapshots.


Yes, Amazon uses SAN equipment that supports snapshots.


This has proven to work very well for me. I had to restore a few 
backups already and it always worked. The bad part is that I need to 
stop the database before performing the Snapshot, for data integrity, 
so that means that I have a hot-standby server only for these snapshots.

Lucas


Actually, you don't need to stop the database. You need to execute 
pg_start_backup() before taking a snapshot and then pg_stop_backup() 
when the snapshot is done. You will need to recover the database when 
you finish the restore but you will not lose any data. I know that 
pg_begin_backup() and pg_stop_backup() are deprecated but since 
PostgreSQL doesn't have any API for storage or file system snapshots, 
that's the only thing that can help you use storage snapshots as 
backups. To my knowledge,the only database that does have API for 
storage snapshots is DB2. The API is called "Advanced Copy Services" or 
ACS. It's documented here:


https://www.ibm.com/docs/en/db2/11.1?topic=recovery-db2-advanced-copy-services-acs

For Postgres, the old begin/stop backup functions should be sufficient.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com