pgbackrest concerns and doubts.

[Ajay Pratap]

Hello,
I am using Postgresql 10 and pgbackrest 2.18 version on centos 7.6
I have few doubts regard pgbackrest.
a) what is the log rotation policy for pgbackrest logs. I see it logs on
default path /var/log/pgbackrest/-.log  what is the
log rotation policy of each logs.
use case: if i am writing a script to parse the logs and gatter some info,
i should be aware of log rotation or if logs doesn't rotate a single file
could be huge to parse.
Or should I simply use *logrotate*


b) since pgbackrest takes physical backup,
 what are the impact if I upgrades minor postgres version(10.5 to 10.10)
and impact on postgres major version(10.10 to 12.X)


Thanks and regards,

-- 


*Disclaimer:* The information in this communication is confidential and 
may be legally privileged. It is intended solely for the use of the 
individual or entity to whom it is addressed and others authorized to 
receive it. If you are not the intended recipient you are hereby notified 
that any disclosure, copying, distribution or taking action in reliance of 
the contents of this information is strictly prohibited and may be 
unlawful. Drishti is neither liable for the improper, incomplete 
transmission of the information contained in this communication nor any 
delay in its receipt. The communication is not intended to operate as an 
electronic signature under any applicable law. Drishti assumes no 
responsibility for any loss or damage resulting from the use of e-mails.

Re: Why are clobs always "0"

[Thomas Kellerer]

Arnie Morein schrieb am 01.12.2019 um 18:31:

I have tested the most recent driver in three different SQL IDEs, and
now with an application I'm writing that uses JDBC metadata, the
comment on a field definition also isn't available as a string
value.

The only thing I ever see regarding data type "text" field values are
either a 0 or a 1; neither of which applies.

So why is this happening, even from the JDBC metadata results as
well?


The Postgres JDBC driver does not have any problems with the "text"
data type, neither with reporting it properly through DatabaseMetaData
(it's reported as Types.VARCHAR) nor with retrieving values from
such a column.

The column size for such a column is reported as 2147483647

Column comments are reliably returned in the column "REMARKS" in the
result of DatabaseMetaData.getColumns()

Given the following table:

create table test (data text);
comment on column test.data is 'The text column';

then the following Java code:

ResultSet rs = connection.getDatabaseMetaData().getColumn(null, "public", "test", 
"%");
rs.next();
System.out.println("column_name: " + rs.getString("COLUMN_NAME"));
System.out.println("column_size: " + rs.getInt("COLUMN_SIZE"));
System.out.println("data_type: " + rs.getInt("DATA_TYPE"));
System.out.println("remarks: " + rs.getString("REMARKS"));

will output:

  column_name: data
  column_size: 2147483647
  data_type: 12
  remarks: The text column

With 12 being the value of java.sql.Types.VARCHAR

Thomas

Sv: Why are clobs always "0"

[Andreas Joseph Krogh]

På søndag 01. desember 2019 kl. 18:31:35, skrev Arnie Morein <
arnie.mor...@mac.com >: 

I have tested the most recent driver in three different SQL IDEs, and now with 
an application I'm writing that uses JDBC metadata, the comment on a field 
definition also isn't available as a string value. 

The only thing I ever see regarding data type "text" field values are either a 
0 or a 1; neither of which applies. 

So why is this happening, even from the JDBC metadata results as well? 

Have you tried the NG-driver: https://github.com/impossibl/pgjdbc-ng 
 
We use it with Blobs/Clobs and it's working good. 

It would help us help you if you mention which IDEs you have tried, and 
provide configuration-paramteres, error-messages etc. 


-- 
Andreas Joseph Krogh 
CTO / Partner - Visena AS 
Mobile: +47 909 56 963 
andr...@visena.com  
www.visena.com  
  

Re: Why are clobs always "0"

[Adrian Klaver]

On 12/1/19 9:31 AM, Arnie Morein wrote:
I have tested the most recent driver in three different SQL IDEs, and 
now with an application I'm writing that uses JDBC metadata, the comment 
on a field definition also isn't available as a string value.


The only thing I ever see regarding data type "text" field values are 
either a 0 or a 1; neither of which applies.


So why is this happening, even from the JDBC metadata results as well?



You are going to have to be more specific:

1) You refer to clob, but the clob datatype is not supported:
https://www.postgresql.org/docs/11/unsupported-features-sql-standard.html
So what are you referring to?

2) Comment on field definition is what exactly?

3) How are you fetching the metadata?


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

Why are clobs always "0"

[Arnie Morein]

I have tested the most recent driver in three different SQL IDEs, and 
now with an application I'm writing that uses JDBC metadata, the 
comment on a field definition also isn't available as a string value.


The only thing I ever see regarding data type "text" field values are 
either a 0 or a 1; neither of which applies.


So why is this happening, even from the JDBC metadata results as well?

Re: MS Access Frontend

[Ron]

On 12/1/19 10:46 AM, Michael Nolan wrote:



On Sun, Dec 1, 2019 at 8:09 AM Martin Gainty > wrote:



that said I think MS missed the boat on 2-phase-commits


Microsoft has never really embraced the concept of a multi-user database 
environment.


You haven't used SQL Server in quite a while (almost 20 years).

  (It doesn't really understand the concept of a multi-user operating 
system, either.)


Neither have you used Windows Server in quite a while (by my experience, 16 
years).



--
Angular momentum makes the world go 'round.

Re: MS Access Frontend

[Michael Nolan]

On Sun, Dec 1, 2019 at 8:09 AM Martin Gainty  wrote:

>
> that said I think MS missed the boat on 2-phase-commits
>

Microsoft has never really embraced the concept of a multi-user database
environment.   (It doesn't really understand the concept of a multi-user
operating system, either.)
--
Mike Nolan

Re: MS Access Frontend

[Martin Gainty]

commenting to Jason that majority of my work in the 90s was read from one DB 
write to another DB
via ODBC2JDBC driver
https://medium.com/openlink-odbc-jdbc-ado-net-data-access-drivers/using-openlinks-odbc-jdbc-bridge-to-access-jdbc-data-sources-via-tableau-windows-edition-89094aa33b7b

that said I think MS missed the boat on 2-phase-commits
https://en.wikipedia.org/wiki/Two-phase_commit_protocol
Two-phase commit protocol - 
Wikipedia
In transaction processing, databases, and computer networking, the two-phase 
commit protocol (2PC) is a type of atomic commitment protocol (ACP). It is a 
distributed algorithm that coordinates all the processes that participate in a 
distributed atomic transaction on whether to commit or abort (roll back) the 
transaction (it is a specialized type of consensus protocol).
en.wikipedia.org

Also 2 factor DB authentication was not implemented by Access
https://en.wikipedia.org/wiki/Database_security
Database security - Wikipedia
Database security concerns the use of a broad range of information security 
controls to protect databases (potentially including the data, the database 
applications or stored functions, the database systems, the database servers 
and the associated network links) against compromises of their confidentiality, 
integrity and availability. It involves various types or categories of 
controls, such ...
en.wikipedia.org

Feel free to ping me offline if you're interested in implementing ODBC2JDBC 
driver as an option
Good Luck!


From: bret_stern 
Sent: Saturday, November 30, 2019 7:14 PM
To: pgsql-general@lists.postgresql.org 
Subject: Re: MS Access Frontend

My two cents. Access is awesome. Extremley fast prototyping environment.
Found on most pc's in businesses. Great reporting tools.
It is my go-to tool for prototyping DB structures, reports, great
import/export tools, and boatloads of people who are glad to share
vba code and solutions

My only bitch is the dumbing down of the application by Microsoft,
Adding bands, and hiding past tools...killing the performance with xml
bs..."there, I feel better now"


Libre Base does alot of the same things, just a different way.

If learning is the point of this discussion, then learn as much as
possible about each one.

I started using win32 back in odd-06 with ODBC and direct db-api calls,
but have steadily drifted towards RAD environments...although there's
great satisfaction and control the lower you go.


When the prototyping is over , I've been pushing Lazarus as my
multi-plateform development environment.

It still pisses me off how abused Excel is, but people don't know the
difference.

Keep on rockin






On 11/30/2019 2:34 PM, Jason L. Amerson wrote:
> I am a Linux user too. I just bought my children Windows laptops so it would
> be easier for them to use. I had to buy me a Windows one too so that I can
> do the whole Microsoft Family thing and monitor them a little. But I think
> it is time to throw them into the deep end and see if they can swim.
>
> Jason L. Amerson
>
>
> -Original Message-
> From: Adrian Klaver 
> Sent: Saturday, November 30, 2019 01:52 PM
> To: Jason L. Amerson ; 'Martin Gainty'
> ; PostgreSQL 
> Subject: Re: MS Access Frontend
>
> On 11/30/19 11:04 AM, Jason L. Amerson wrote:
>> Thanks Martin. I have decided to go another route. I have nothing but
>> problems whenever I use Microsoft products. I personally think that
>> Microsoft was shit when it started, and it is still shit 35 years later.
>> So, I am just going to take Windows off my computers and put Linux on
>> them and just use a different client.
>
> I'm a Linux user and I applaud your move. Just be aware you will not find an
> Access replacement on Linux. You will find things that have subsets of its
> functionality, but not a drop in replacement.
>
>
>>
>> Jason L. Amerson
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
>
>

Re: MS Access Frontend

[Vincenzo Campanella]

Il 30.11.2019 19:52, Adrian Klaver ha scritto:

On 11/30/19 11:04 AM, Jason L. Amerson wrote:
Thanks Martin. I have decided to go another route. I have nothing but 
problems whenever I use Microsoft products. I personally think that 
Microsoft was shit when it started, and it is still shit 35 years 
later. So, I am just going to take Windows off my computers and put 
Linux on them and just use a different client.


I'm a Linux user and I applaud your move. Just be aware you will not 
find an Access replacement on Linux. You will find things that have 
subsets of its functionality, but not a drop in replacement.


What about Lazarus IDE?

https://www.lazarus-ide.org/

Free, cross platform, relatively complete and easy to use, based on 
FreePascal, with reporting tools included...

RE: Re: Re: Postgres Full Text Search Jsonb Array column does not search for first row

[Dmytro Zhluktenko]

The issue was solved with the approach suggested here.https://stackoverflow.com/questions/59049873/postgres-full-text-search-jsonb-array-column-does-not-show-first-row BR, Dmytro. From: Laurenz AlbeSent: 28 листопада 2019 р. 16:39To: Dmytro Zhluktenko; pgsql-general@lists.postgresql.orgSubject: Re: Re: Postgres Full Text Search Jsonb Array column does not search for first row On Wed, 2019-11-27 at 11:54 +0200, Dmytro Zhluktenko wrote:> explain (analyze, BUFFERS)> SELECT *> FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'fir:*'::tsquery> > outputs this query plan:> Bitmap Heap Scan on "Repro" x  (cost=12.00..16.26 rows=1 width=72) (actual time=0.007.0.007 rows=0 loops=1)>   Recheck Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery)>   Buffers: shared hit=2>   ->  Bitmap Index Scan on repro_fts_idx  (cost=0.00..12.00 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)> Index Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery)> Buffers: shared hit=2> Planning Time: 0.070 ms> Execution Time: 0.040 ms> > Query runs fine if uses seq scan. Seq Scan is not desired here since this is the query that should run on huge amounts of data and it should find the first element.> Obviously, if seq_scan is off, then query still does the same result.> > Also, if you add 10 more entries, it will still fail to find the first one using index. I cannot quite follow. We have seen that the query can use the index by setting "enable_seqscan = off",but that PostgreSQL prefers to use a sequential scan because the table is small. If the table were bigger, PostgreSQL would prefer the index scan. Are your concerns hypothetical or real?If real, can you show EXPLAIN (ANALYZE, BUFFERS) output of a queryexecution where PostgreSQL chooses a sequential scan, but you thinkit shouldn't? Yours,Laurenz Albe-- Cybertec | https://www.cybertec-postgresql.com