Re: vacuumdb seems not to like option -j when run from crontab

[Joshua Drake]

8.4 does not support -j

On Mon, Dec 4, 2023 at 8:22 AM Ron Johnson  wrote:

> (Sorry for top-posting.  Blame gmail.)
>
> Turns out that PG 8.4.20 is also installed from the RHEL repository.
>
> Thanks.
>
> On Mon, Dec 4, 2023 at 11:13 AM Alan Hodgson 
> wrote:
>
>> On Mon, 2023-12-04 at 11:07 -0500, Ron Johnson wrote:
>>
>> PG 9.6.24 (Yes, it's EOL.)
>>
>> When running "vacuumdb -p5433 -j4 --analyze tap_d" from a bash prompt, it
>> works as expected:
>> $ vacuumdb -p5433 -j4 --analyze tap_d
>> vacuumdb: vacuuming database "tap_d"
>>
>> But not when running from crontab:
>> vacuumdb -p5433 -j4 --analyze tap_d
>> vacuumdb: invalid option -- 'j'
>> Try "vacuumdb --help" for more information.
>>
>> Obviously I'm missing something, but don't see what it is.  Attached is
>> the script it runs from.
>>
>>
>> Is your user and PATH the same? ie. are you running the same vacuumdb
>> executable?
>>
>>

Re: Postgres calendar?

[Joshua Drake]

Bruce,

It would certainly help in keeping track of things.

JD

On Tue, Oct 4, 2022 at 2:02 PM Bruce Momjian  wrote:

> Would people be interesting in subscribing to a Postgres calendar that
> includes dates for minor releases, final minor release dates for major
> versions, commit fests, and even Postgres events?  For example, it could
> include information from:
>
> https://www.postgresql.org/developer/roadmap/
> https://www.postgresql.org/support/versioning/
> https://commitfest.postgresql.org/
> https://www.postgresql.org/about/events/
>
> We could even add information about beta, release candidate, and final
> major releases, though the final release dates are usually not public.
>
> This could be done in Google Calendar, with an exported ICS file, or via
> a dedicated ICS file.  I could even automate it by scraping our website.
>
> --
>   Bruce Momjian  https://momjian.us
>   EDB  https://enterprisedb.com
>
>   Indecision is a decision.  Inaction is an action.  Mark Batterson
>
>
>
>

Re: Logical replication of large objects

[Joshua Drake]

Large objects are largely considered a deprecated feature.

Though I like the idea, was there any consensus on -hackers?

JD

On Sun, Jun 5, 2022 at 2:23 AM Andreas Joseph Krogh 
wrote:

> I started this thread 5 years ago:
> https://www.postgresql.org/message-id/flat/7c70d9bd-76fc-70fa-cfec-14f00a4a49c3%40matrix.gatewaynet.com#15cbf1c82be9341e551e60e287264380
>
>
>
> We'd be willing to help funding development needed to support Large Object
> logical replication.
>
> Anyone interested?
>
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>

Re: Sharing DSA pointer between parallel workers after they've been created

[Joshua Drake]

Marcus,

This is probably better suited for -hackers.

JD

On Wed, Jun 8, 2022 at 8:00 PM Ma, Marcus  wrote:

> Hey,
>
>
>
> I’m currently working on a parallelization optimization of the Sequential
> Scan in the codebase, and I need to share information between the workers
> as they scan a relation. I’ve done a decent amount of testing, and I know
> that the parallel workers all share the same dsa_area in the plan state.
> However, by the time I’m actually able to allocate a dsa_pointer via
> dsa_allocate0(), the separate parallel workers have already been created so
> I can’t actually share the pointer with them. Since the workers all share
> the same dsa_area, all I need to do is be able to share the single
> dsa_pointer with them but so far I’ve been out of luck. Any advice?
>
>
>
> Marcus
>

Re: Cluster OID Limit

[Joshua Drake]

Lucas,

If you run out of OIDs you are doing something wrong. We haven't supported
user space OIDs in a lot of releases. Which release are you using?

JD

On Thu, Jun 9, 2022 at 2:11 AM Lucas  wrote:

> Hello,
>
> In the company I work for, some clusters reached the OID limit (2^32) and
> we had to reinstall the cluster.
>
> I was wondering if there is any discussion on:
> * "compress" the OID space
> * "warp around" the OID space
> * segment a OID range for temporary tables with "wrap around"
>
> --
> Lucas
>

Re: Ubuntu 14.04 (trusty) Postgres 13 deb package

[Joshua Drake]

On Sun, Jun 27, 2021 at 10:42 AM Bhavesh Mistry 
wrote:

> Hi All,
>
> I was trying to install PSQL 13 on Ubuntu 14.04 (trusty)  but I could not
> find the package.  It seems the path has been removed.  Can you please tell
> me how to build deb package for trusty from psql source? I tried psql 13
> sources, I was able to build but I could not figure out how to build deb
> package.  I would appreciate any pointers.
>
>
Upgrade your Linux, 14.04 has been EOL for 2 years.


> --
> Thanks,
>
> Bhavesh
>
>

Re: bottom / top posting

[Joshua Drake]

>
>
> > It
> > could probably be argued that the prohibition was more relevant in the
> days
> > before 99% of users read their email  via clients which hide quoted
> content
> > unless it is explicitly expanded.
>
> Yeah, gmail is aggressively unfriendly for this purpose.  But if you
> can't be bothered to trim your quotes, I'd actually much rather that
> you top-post.
>
> regards, tom lane
>

At least the students haven't discovered this list yet and are posting
their homework assignments (like they do on chat channels).

JD

Re: PL/java

[Joshua Drake]

>
>
>
> The PL/java “add-on” is not supported directly by Postgres.
>
>
With a few exceptions (Perl, Python) this is how the community handles all
"add-ons". A great number of features available to you are available as
third party extensions and not part of core. Zombodb, Timescale and Citus
are good examples.

In short, don't worry about whether or not it is "official".

JD


> I would like to ask if there is a plan to include it as an official
> procedural language. If so, when?
>
> Thanks,
> *—*
> *Zé Rui Marques*
>
>
>

Re: Multiple result set not displayed in PgAdmin4

[Joshua Drake]

Howdy,

I believe you would receive more help from the proper forum for PgAdmin4:

https://www.pgadmin.org/support/list/

This is a list for PostgreSQL and it's software. PostgreSQL's client is
psql which is a command line client.

Thanks!
JD



>>
>>

Re: pg_dump - how to force to show timestamps in client log

[Joshua Drake]

Howdy,

pg_dump is just issuing SQL commands, you could turn on timestamps in your
postgresql log.

JD

On Fri, Nov 20, 2020 at 10:02 AM Durumdara  wrote:

> Hello!
>
> We need to log the pg_dump's state.
> What objects are in copy, and what are the starting and ending times.
>
> But when I try to redirect the output, the result doesn't have timestamps.
>
> PG 11, on Windows.
>
> As I see the -v option isn't enough to see the starting times.
>
> For example:
>
> 2020-11-19 12:00:01.084 Dump table content table1
> 2020-11-19 12:03:12.932 Dump table content table2
> ...
> etc.
>
>
> Thank you for any information you can provide!
>
> dd
>
>
>

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Joshua Drake]

Howdy,

First let me say thanks for the feedback! It is rare that we receive
detailed "user feedback" on these lists so it is good to hear from the
outside world. I am only going to address a few things as others have
addressed the rest.


> 2. pg_dump misinterprets non-ANSI values for the "--exclude-*" options (at
> least the --exclude-table-data one, which is the one I've tested) on
> Windows, resulting in it being impossible to make more "sophisticated"
> backups of PostgreSQL databases; it's either all or nothing. Other
> programs, including my own test scripts and commands, are perfectly able to
> use any Unicode character sent from/through both cmd.exe and PHP CLI, but
> not pg_dump, so the idea that "Windows it at fault" here just doesn't seem
> true. (Although I don't doubt for a second that it often *is* the case...
> Microsoft is not a nice entity in any way.) I spent a lot of time and
> efforts experimenting with and asking about this, but eventually gave up
> and concluded that it was yet another bug in an open source project "only"
> on Windows with no real/pressing interest in fixing it. For me, this means
> that I lose a ton of fresh data every day, or have to make *gigantic*
> backups. (I have several huge "temporary debug log" tables whose data have
> zero long-term value but tons of short-term value.) It makes me feel
> crippled and excluded in an uncomfortable manner.
>
>
I have to agree that pg_dump is largely a step child backup program. It has
consistently been found over the years to be lacking in a number of areas.
Unfortunately, working on pg_dump isn't sexy and it is difficult to get
volunteers or even paid resources to do such a thing. The real solution for
pg_dump is a complete refactor which includes pg_dumpall and it is not a
small undertaking. It should be noted that it is also a less and less used
program. On our team it is normally used for only very specific needs
(grabbing a schema) and we use binary backups or logical replication to
receive specific data.



> 3. The ability to embed PG to run in an automatic, quiet manner as part of
> something else. I know about SQLite, but it's extremely limited to the
> point of being virtually useless IMO, which is why I cannot use that for
> anything nontrivial. I want my familiar PostgreSQL, only not require it to
> be manually and separately installed on the machine where it is to run as
> part of some "application". If I could just "embed" it, this would allow me
> to create a single EXE which I can simply put on a different machine to run
> my entire "system" which otherwise takes *tons* of tedious, error-prone
> manual labor to install, set up and maintain. Of course, this is probably
> much easier said than done, but I don't understand why PG's architecture
> necessarily dictates that PG must be a stand-alone, separate thing. Or
> rather, why some "glue" cannot enable it to be used just like SQLite from a
> *practical* perspective, even if it still is a "server-client model"
> underneath the hood. (Which doesn't matter at all to me, nor should it
> matter to anyone else.)
>
>
This is really using the wrong tool for the job type of issue. PG was never
designed for such a scenario.


> 4. There is no built-in means to have PG manage (or even suggest) indexes
> on its own. Trying to figure out what indexes to create/delete/fine-tune,
> and determine all the extremely complex rules for this art (yes, I just
> called index management an *art*, because it is!), is just utterly hopeless
> to me. It never gets any easier. Not even after many years. It's the by far
> worst part of databases to me (combined with point five). Having to use
> third-party solutions ensures that it isn't done in practice, at least for
> me. I don't trust, nor do I want to deal with, external software and
> extensions in my databases. I still have nightmares from PostGIS, which I
> only keep around, angrily, out of absolute necessity. I fundamentally don't
> like third-party add-ons to things, but want the core product to properly
> support things. Besides, this (adding/managing indexes) is not even some
> niche/obscure use-case, but something which is crucial for basically any
> nontrivial database of any kind!
>

I think you are looking at this from a very windows centric way. Open
Source has its origins from the Unix paradigm where each tool was designed
to solve one type of problem and you used multiple tools to create a
"solution". Though we have strayed from that on some items due to the
evolving nature of software needs, that is still at our core and for good
reason. Having tools, flags etc... to do such things (including your point
#3) creates complexity best left to "vendors" not the software project.


>
> 5. Ever since my early days with PG in the mid-2000s, I've tried numerous
> times to read the manual, wikis and comments for the configuration files,
> specifically the performance directives, and asked many, many times for
> help about that, ye

Re: Function Speed vs UI Function Speed

[Joshua Drake]

On Mon, Aug 31, 2020 at 10:32 AM Susan Hurst 
wrote:

>
> For example, a batch load script that inserts multiple rows into a table
> may call an insert function within a cursor to populate each row into
> the target table. Meanwhile, a non-technical business user may want to
> create only one entity via a UI. However, the UI must have validations
> and helpful messages to to user so the insert worksor won't, but at
> least user would know what they need to do differently to get to a
> successful outcome.
>

I think you have both. You don't want the round trip of having the database
throw an exception in this example. So yes, you have the validations within
the UI for a higher level of success before submission. However, you still
want the proper constraints on the database itself as it is the canonical
source for your data. Further, you never know when someone else is going to
connect to the database in something that isn't your application.

JD

Re: Logical replication

[Joshua Drake]

Javi,

What does your PostgreSQL log say about replication?

JD

On Wed, Jun 17, 2020 at 2:07 AM Javi Legido  wrote:

> Good morning.
>
> I'm testing logical replication, but after creating subscription nothing
> happens, and I expected replication start.
>
> Details here: https://paste.debian.net/1152451/
>
> Any help will be appreciated.
>
> Thanks.
>
> Javier
>

Re: Minor Upgrade Question

[Joshua Drake]

Susan

You can use -Uvh to upgrade the rpms on the existing machine. You can then
use symlinks to link the expected pgsql data directories. Make sure you
take a backup, and stop the service before you proceed.

JD


On Tue, Jun 16, 2020 at 7:12 AM Susan Joseph 
wrote:

> So when I first started working with PostgreSQL I was using the latest
> version (11.2).   I don't want to move to 12 yet but I would like to get my
> 11.2 up to 11.8.  Due to my servers not being connected to the Internet I
> ended up downloading the libraries and building the files locally.  My
> question is how do I upgrade to 11.8?  I know how to go and get the rpms
> now and download those to a disconnected server and then install PostgreSQL
> that way.  I was able to install 11.8 on another server using the rpms.
> But my directories are different.  The rpm install placed the files into
> the directory /data/pgsql-11.  My 11.2 database is in /data/pgsql.  I
> checked the rpm file and it says that the files are not relocatable.  So I
> can do a new install of 11.8 via the rpms and it will place the files into
> /data/pgsql-11, can just need to copy the executable files in the
> /data/pgsql-11/bin directory into my /data/pgsql/bin or are there other
> files that need to be copied over?  Is there a better way to do this rather
> than reinstalling postgreSQL again on a server that already has it?
>
> Thanks,
>   Susan
>

Re: PostgreSQL native multi-master

[Joshua Drake]

In this instance, BDR from 2ndquadrant is probably your best bet. It is
commercial but they are a community supporting company.

JD

On Wed, Apr 8, 2020, 08:53 Stephen Frost  wrote:

> Greetings,
>
> * Vano Beridze (vanua...@gmail.com) wrote:
> > What are the plans to support multi-master natively?
> > What solution would you recommend at this point? preferably free.
>
> You probably want to look at logical replication, which allows you to
> replicate data from one PG server to another (with both allowing
> writes).  This doesn't have any conflict resolution today, so you have
> to make sure there aren't any conflicts or you might end up breaking the
> replication.
>
> Having conflict resolution included in core would certainly be nice but
> I'm not aware of anyone currently working on it.  I'm sure there are
> organizations out there who are committed to open source and who would
> be happy to work with you to add that capability to PG though.
>
> Thanks,
>
> Stephen
>

Re: full text

[Joshua Drake]

Roberto,

I would look at ZomboDB: https://www.zombodb.com/ . Which is exactly what
you suggest.

JD

On Wed, Apr 8, 2020 at 10:28 AM Roberto Della Pasqua <
roberto.dellapas...@live.com> wrote:

> Please sorry because I’m newbie of PGSQL
>
>
>
> I need the best performing and overall quality full-text search, can be
> possible to have the data stored in pgsql and the index to elasticsearch?
> Can be in sync between?
>
>
>
> Thank you
>
>
>
> Btw. Do you suggest another engine than elastic?
>
>
>
> Roberto Della Pasqua
>
> www.dellapasqua.com
>
>
>