Re: pg_dumpall - restoration problem- resolved

just erased the collation from that field. Still no idea on how it came to be there. Cheers Tony On Sunday 7 April 2024 10:35:44 AM ACST Tom Lane wrote: > Tony Bazeley writes: > > I've a problem with restoring a cluster created with pg_dump_all from 14.8 > > ( pg_dumpall

pg_dumpall - restoration problem

ething specific to postgres16 I installed a copy of the postgres14 (14.11) with failure in exactly the same way. I don't understand the class text COLLATE pg_catalog."C.UTF-8" syntax, but select * from pg_collation shows a C.UTF8 but no C.UTF-8 Any help on how to proceed would be most appreciated TIA Tony Bazeley

Re: Large scale reliable software system

ead: we have one system that involves processing through large amounts of data for reports / queries. Shipping all that back through the ORM / db interface (ODBC / JDBC / psycopg2 / whatever for resolution / filtering on the front end application where SQL / procedures / views could do that in the DB and just ship back the required data seems counterproductive. Tony Shelver >

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

ut we prefer a managed solution provided by some cloud infra providers so the context I'm getting here will allow me to better understand our provider's solution and their road map. Thanks Tony

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

rk, yes, each cluster (which is an entier >> PostgreSQL system) would be able to have its own KEK. >> >> There's a bit of overhead from each cluster and each would have their >> own shared buffers pool of memory and such. >> > Thanks Tony

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

sters then? Better isolation of the customers, but still on one server. On Thu, May 18, 2023 at 3:53 PM Stephen Frost wrote: > Greetings, > > Please don't top-post on these lists. > > * Tony Xu (tony...@rubrik.com) wrote: > > Our use-case is for a multi-tenancy

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

r, it also makes sense to allow them to have completely independent encryption facilities? Thanks, Tony On Thu, May 18, 2023 at 8:54 AM Stephen Frost wrote: > Greetings, > > * Tony Xu (tony...@rubrik.com) wrote: > > The FAQ (copied below) mentioned that native transparent data

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

Ok, thanks for the clarification. Any particular reason for the change of plans? Would it come in 17? Thanks Tony On Wed, May 17, 2023, 5:49 PM Bruce Momjian wrote: > On Wed, May 17, 2023 at 03:35:39PM -0700, Tony Xu wrote: > > Hi There, > > > > The FAQ (copied below)

Would PostgreSQL 16 native transparent data encryption support database level encryption?

? Thanks Tony *https://www.postgresql.org/about/press/faq/ <https://www.postgresql.org/about/press/faq/>* *Q: What features will PostgreSQL 16 have?A: As always, we can't be certain what will go in and what won't; the project has strict quality standards that not all patches

Re: Best Open Source OS for Postgresql

Copied to the list On Wed, 1 Feb 2023 at 08:18, Tony Shelver wrote: > > > On Wed, 1 Feb 2023 at 08:04, Tony Shelver wrote: > >> >> On Tue, 31 Jan 2023 at 15:10, Marc Millas wrote: >> >>> Sorry for inappropriate "reply". >>> >>>

Re: Is there a guide to use PostgresSQL as alternative to MariaDB/MySQL in OpenStack?

On Sat, 5 Nov 2022 at 01:15, Larry Sevilla wrote: > Hi, > > I'm studying OpenStack Zed using Ubuntu 22.04 with the following guides: > > https://docs.openstack.org/install-guide/ > > And I have successfully installed OpenStack services and tested Horizon. > > > But the guide uses MariaDB/mysql. >

Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed

On Thu, 11 Aug 2022 at 12:00, Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi Tony > > The reason I'm looking to do it from scratch is that its a case of "once > bitten, twice shy". > > This CMS will be replacing a Joomla based CMS. > >

Fwd: Modelling a web CMS in Postgres ... a little advice needed

From: Tony Shelver Date: Thu, 11 Aug 2022 at 11:47 Subject: Re: Modelling a web CMS in Postgres ... a little advice needed To: Laura Smith On Thu, 11 Aug 2022 at 09:35, Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi > > I'm looking at using pgsql as a back

Re: simple reporting tools for postgres in aws

LibreOffice Base? I have used LibreOffice Base to spin up a quick data entry front end to Postgresql. Has some basic reporting functionality as well, and can integrate to other LIber\Office products. Poor man's version of MS Access. As for other tools, https://querytreeapp.com seems to have som

Re: New "function tables" in V13 documentation

On Mon, 9 Nov 2020 at 02:54, Adrian Klaver wrote: > On 11/8/20 1:57 PM, Thomas Kellerer wrote: > > In case someone is interested: there is a little discussion going on on > > Reddit whether the new format of presenting functions in V13 is a step > > backwards: > > > > > > > https://www.reddit.com

Re: JSONB order?

On Thu, 5 Nov 2020 at 18:27, Rob Sargent wrote: > > > On Nov 5, 2020, at 8:45 AM, Tony Shelver wrote: > > > > -- Forwarded message - > From: Tony Shelver > Date: Thu, 5 Nov 2020 at 17:45 > Subject: Re: JSONB order? > To: Christophe Pettus >

Fwd: JSONB order?

-- Forwarded message - From: Tony Shelver Date: Thu, 5 Nov 2020 at 17:45 Subject: Re: JSONB order? To: Christophe Pettus Thanks Christophe, that's what I thought. Just seemed weird that they were 'disordered' in exactly the same way every time. FYI, as of Py

JSONB order?

quot;: "No", "Nausea, vomiting, diarrhea, abdominal pain": "No", "Runny nose/stuffy nose or nasal congestion": "No", "Difficulty breathing or shortness of breath": "No", "Not feeling well, extreme tiredness, sore muscles": "No", "Have you travelled outside of Canada in the past 14 days?": "No", "Have you had close contact with a confirmed or probable case of COVID-19?": "No" } If the order had remained the same, it's child's play to pull the data out and present it in a report, even if the data elements change. But... seen above, the order gets mixed up. Any ideas? Thanks Tony Shelver

Re: What's your experience with using Postgres in IoT-contexts?

iness requirements, everything is needed at the cloud / server level for analysis. On Wed, 7 Oct 2020 at 08:28, Thorsten Schöning wrote: > Guten Tag Tony Shelver, > am Dienstag, 6. Oktober 2020 um 16:33 schrieben Sie: > > > Each vehicle reports position to the database at least every 30 sec

Re: What's your experience with using Postgres in IoT-contexts?

On Mon, 5 Oct 2020 at 16:34, Thorsten Schöning wrote: > Guten Tag Tony Shelver, > am Montag, 5. Oktober 2020 um 15:44 schrieben Sie: > > > Not sure about PG in that environment. Have you thought about something > > like H2 java database? https://www.h2database.com/html/ma

Re: What's your experience with using Postgres in IoT-contexts?

Not sure about PG in that environment. Have you thought about something like H2 java database? https://www.h2database.com/html/main.html That is included as the standarDB in a vehicle tracking system we use, although we have re[placed with PG. On Mon, 5 Oct 2020 at 11:20, Thorsten Schöning wrot

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

> > > On 9/23/20 11:51 AM, tutilu...@tutanota.com wrote: > > Huh? A schema is just a name space, why does it matter how the > extension chooses to define it? I mean you could have number of > permutations of postgis. > > I'm saying that PostGIS has a bug due to incorrectly constructed >

Re: PostgreSQL on Windows' state

A quick Google search came up with this: https://www.npgsql.org. No experience with it however. We moved to Python just before we moved to Postgresql. The key motivator for C# at the time is now more of a React / JS proponent Everything in our stack apart from 1 proprietary Java / Oracle sys

Fwd: "Go" (lang) standard driver

-- Forwarded message - From: Tony Shelver Date: Tue, 18 Aug 2020 at 09:33 Subject: Re: "Go" (lang) standard driver To: Edson Richter A quick Google search found https://github.com/lib/pq. Has 6.1K stars so I would guess fairly well used. On Tue, 18 Aug 2020 at 05

Re: Querying PostgreSQL / PostGIS Databases in Python

system written in java on top of Postgres, where the queries are done via the java ORM system and the GIS processing is done in Java. This is even more true where the python client is situated on physically separate servers to the database. On Tue, 4 Aug 2020 at 14:09, Tony Shelver wrote: > We

Re: Querying PostgreSQL / PostGIS Databases in Python

We are using python on top of Postgresql / PostGIS, for a vehicle tracking system. THis is quite data intensive, and we have some 'interesting' GIS queries where we see where a vehicle or fleet has stopped within specific areas, where it has traveled, any incidents along the way and much more. Pos

Re: pg_dump of database with numerous objects

On 2020-05-31 13:08, Christophe Pettus wrote: On May 31, 2020, at 08:05, t...@exquisiteimages.com wrote: My pg_class table contains 9,000,000 entries and I have 9004 schema. Which version of pg_dump are you running? Older versions (don't have the precise major version in front of me) have N^2

Re: pg_dump of database with numerous objects

On 2020-05-31 11:24, Adrian Klaver wrote: On 5/31/20 8:05 AM, t...@exquisiteimages.com wrote: I have always used pg_basebackup to backup my database and I have never had any issues. I am now needing to upgrade to a new version of PostgreSQL and I am running into problems when pg_upgrade calls

pg_dump of database with numerous objects

I have always used pg_basebackup to backup my database and I have never had any issues. I am now needing to upgrade to a new version of PostgreSQL and I am running into problems when pg_upgrade calls pg_dump. pg_dump stalled at: "pg_dump: saving database definition" for 24 hours before I kille

Re: Upgrading from PostgreSQL 9.3 to 11.6

On 2019-12-20 09:02, Justin wrote: Hi Tony Can you describe the database in greater detail? how many tables, type of data, how big is the down time window? So this will be on new server hardware, and new DB install, what OS? and extensions being used, has there been any testing with

Upgrading from PostgreSQL 9.3 to 11.6

I have a PostgreSQL 9.3 installation that I finally have time to migrate to a currently supported version. The server that the installation is installed on is also at its end of service. Can anyone help me with completing this conversion as securely and efficiently as possible with the least a

Re: Return Table in StoredProceure/Function

Well then SQL Server breaks that rule big time :) Most people coming from a SQL Server background expect procedures to return a result set that can be queried, and in-out or out parameters to return variables for further information. On Wed, 20 Nov 2019 at 17:20, Thomas Kellerer wrote: > İlyas

Re:

Research Postgraphile, it provides an easy-to-use GraphQL interface to Postgres. Postgraphile.org On Wed, 20 Nov 2019 at 17:00, Soukaina Lahchiouach wrote: > Hello , > does postgresql with react native allow us to create chat applications? >

Re: pgmodeler ?

<< On Sun, 1 Sep 2019 at 13:45, Thiemo Kellner wrote: > Quoting Olivier Gautherot : > > This is the specific error message: > >> >> Could not execute the SQL command. >> Message returned: ERROR: column pr.proisagg does not exist >> LINE 1: ...namespace AS ns ON pr.pronamespace

Re: SQL equivalint of #incude directive ?

Similar to what Francisco said. Not exactly sure what your use case is though.. In the past I have usually used a DB modeling / design front end tool to design my database, and then maintain and generate most of the build scripts. Datanamic Dezign used to be my go-to for SQL Server when i still p

Re: How to set up PostGIS to support ArcGIS, MapInfo and QGIS desktop users?

This is probably the wrong place to ask. Have a look at the POSTGis support options page. Stack Exchange has a lot of info. Also go to the particular products that you are interested in, they should have their requirements listed. You will likely also want to set

Re: Recomended front ends?

On Wed, 7 Aug 2019 at 20:57, stan wrote: > I am in the process of defining an application for a very small company > that uses Postgresql for the backend DB. This DB will eventually run on a > hosted machine. As you imagine all of the employees have Windows machines > for their normal work asks.

Re: Does pgadmin4 work with postgresql 8.4?

8.4's final release was 5 years ago, so unlikely anyone would know the answer to your question. The only way to really know is to try it. I just had an issue with the current version (4.11) against PG11.4, where the version of psycopg2 was out of date by a release (2.7x instead of 2.8). Most stuf

Re: PGAdmin4.11.1 on Ubuntu 18.04

> >> What repo are you getting the packages from? > > Repo looks like PostgreSQL apt repository https://apt.postgresql.org. .../bionic-pgdg.main > -- > Adrian Klaver > adrian.kla...@aklaver.com >

Re: PGAdmin4.11.1 on Ubuntu 18.04

The article at stackoverflow is here <https://stackoverflow.com/questions/57226520/select-does-not-return-values-postgres-11-4> On Thu, 1 Aug 2019 at 17:28, Tony Shelver wrote: > Thanks for the replies: the version is PG11.4. > > As for needing to upgrade: I just want to fix the

Re: PGAdmin4.11.1 on Ubuntu 18.04

7:39 AM, Tony Shelver wrote: > > I am getting an 'able oid' when querying any tables using the Query tool. > > When using the view / edit data option, everything works fine. > > Should have asked in previous post: > > What Postgres version(s)? > > > > &

PGAdmin4.11.1 on Ubuntu 18.04

I am getting an 'able oid' when querying any tables using the Query tool. When using the view / edit data option, everything works fine. I came across this <<< The problem is due to python3-psycopg2. The latest pgadmin4 version requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt ins

Re: DRY up GUI wiki pages

> I created the first one because the second one was full of old, stale, > useless things. I believe that everything valid on the second one was added > to the first one at the time. > > Also look at https://wiki.postgresql.org/wiki/Design_Tools and > https://wiki.postgresql.org/wiki/Documentation_

Re: Analyze command running for 2063 minutes so far

On 2019-06-28 10:15, Tom Lane wrote: t...@exquisiteimages.com writes: I started an Analyze command on a database Wednesday evening at around 9:00PM. it is now Friday morning at 8:00 and it is still running. ... I did try to execute: SELECT pg_cancel_backend(4029); and SELECT pg_terminate_backend

Analyze command running for 2063 minutes so far

Hello, I started an Analyze command on a database Wednesday evening at around 9:00PM. it is now Friday morning at 8:00 and it is still running. If I execute the query: select max(last_analyze) from pg_stat_user_tables; The result I receive is: 6/27/2019 8:27 AM So it appears that no analyze

Re: Data entry / data editing tools (more end-user focus).

tps://aurelia.io/ (JS) or CUBA https://www.cuba-platform.com/ > (Java) > > :Stefan > > Am Do., 28. März 2019 um 15:39 Uhr schrieb Adrian Klaver > : > > > > On 3/27/19 11:49 PM, Tony Shelver wrote: > > > > Please reply to list also, more eyes on the the problem. >

Re: bigint out of range

Adding to what David said, I don't see much point of having a bigint status. Usually status attributes are a fixed set of values that can be checked programmatically. >

Re: Postgres for SQL Server users

list of extensions for datatypes, languages, specialized functions and so on. Things like PostGIS (already mentioned), PipelineDB and so on, plus Postgres-based things like Timescale, Citusdata and so on. On Tue, 7 May 2019 at 07:35, Tony Shelver wrote: > I have to agree on the geospat

Re: Postgres for SQL Server users

I have to agree on the geospatial (GIS) features. I converted from SQL Server to Postgresql for our extended tracking database. The SS geospatial feature set doesn't seem nearly as robust or complete or perfoirmant as that supplied by PostGIS. The PostGIS ecosystem of open source / 3rd party tools

Data entry / data editing tools (more end-user focus).

Looking for a good tool that I can give to users to enter data (for example, products, categories, brands, price tables and so on). Preferably it should also allow images to be copied into a bytea field but I know I can't have everything. Been battling with a few open source 'headless' content man

Re: Key encryption and relational integrity

Not in Europe, but have worked a bit with medical records systems in the USA, including sharing across providers. The primary key of the user is _should_ be system generated, and this is meaningless from a user identity standpoint. If you encrypt user name and other significant personal data on t

Re: When to store data that could be derived

Not the answer you are looking for, but... I'd suggest trying to create a non-trivial set of dummy data to test your assumptions before deciding on a route. It's saved my (professional) life a few times over the years when dealing with untested designs and new (to us) technology. Some years ago w

Re: Forks of pgadmin3?

s in psql but it’s not handy with many columns. > > For that reason we currently stay with pgadminIII (and this is for us also > one of several reasons to delay any move from 9.6 to a more recent version). > > > > Klaus > > > > *Von:* Tony Shelver > *Gesendet:* Fr

Re: Forks of pgadmin3?

Or just persevere with pgadmin4 for a few months? Pretty common for people to hate any major changes to a tool that they are very comfortable with. This year I've invested the time to learn a few new toolsets (not on Postgresql necessarily) and found it to be well worth while. At least pgAdmin4

Re: Tools to migrate data from Json files to PostgreSQL DB.

You may want to assess how you want to store and access the data in Postgres before deciding on an import strategy. I have a system with a mix of relational and JSON data. The data was originally sourced in flat file format. I wrote a few Python programs to take the data, then format to JSON, whi

Re: Help : Update and insert record based on several value in the parameter

I don't know what you are using as a front end to call this update, but I have been finding json/jsonb parameters passed into a function / procedure and then using postgres json functions to process the data as a good solution. If your data fits postgres array datatype, as others have mentioned, t

Re: the installation of pgadmin4 makes me weep in frustration

I know this has already been answered, but for those who are also python users, there is another and probably better option: If you already have a python 3.6+ installation, you can download the pgadmin4 wheel (python installable) from https://www.pgadmin.org/download/ Then do a pip install on it

Re: the installation of pgadmin4 makes me weep in frustration

Just over a year ago, I started to look for free / open source clients for Postgres, and went through most of that list. Unless you are willing to pay for a commercial license, most of them are not close to PG or have some limitations that made them unusable for me. Several 'free' tools were limit

Re: Benchmark of using JSON to transport query results in node.js

I'm fairly new to Postgres, but one question is how node.js implements the native driver when fetching the data: fetchall, fetchmany or fetch.single? Also which native driver is it using? Does the native driver do a round trip for each record fetched, or can it batch them into multiples? For examp

Re: Pulling data from Postgres DB table for every 5 seconds.

I am not familiar with Aurora, but.. What something like https://github.com/subzerocloud/pg-amqp-bridge? Set up a message queue in Postgres, which calls into AMPQ (RabbitMQ) to send a message for consumption by one or more clients. This provides a function that can be called from a trigger to se

Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI

Just a side comment: Why use phpPgAdmin when pgAdmin 4.6 is current, free and readily available? It also has a graphical table-from-file loader as well. On Fri, 7 Dec 2018 at 23:35, Adrian Klaver wrote: > On 12/7/18 9:04 AM, s4...@yahoo.co.jp wrote: > > I didn't specify any schema, so it was cr

Re: querying both text and non-text properties

I would suggest doing testing out btree_gin with a non-insignificant amount of data before going ahead with it. I did a test case last month, and the size of the generated index was _much_ bigger than the base table. The case involved a compound key if 1 int column and 1 timestamp range column. O

Re: Question about index on different tablespace and rebuild it

Did you check the documentation for alter index? https://www.postgresql.org/docs/10/sql-alterindex.html You could create a script file (plenty of examples on the internet on generating these) and then run through psql or whatever. Also, if you just have a few indexes to move, you could use (for e

Re: editable spreadsheet style interface

For a quick and dirty data editor, LibreOffice Base seems to work fine. On Tue, 30 Oct 2018 at 23:05, Tim Clarke wrote: > On 30/10/2018 20:32, Martin Mueller wrote: > > > > I have used Aqua Data Studio for several years. Jetbrains recently > > released a similar product. Academic licensing is af

Re: Function for Exception Logging

I'd be interested if there is an answer to this. The big issue with writing to a table is the interaction with rollbacks and commits. Even more difficult in PG functions as they have no commit / rollback capability. I haven't played with stored procedures in in PG11 yet. In Oracle, I wrote log

Time-based table design / index selection

dt_range starting at the time of the change, where applicable. I would use the && operator to get the current records based on the range. Another alternative could be to implement the active range using separate date fields and a btree index to cover the reln_typ_id, obj_id_owner_of, active_dt_from and active_dt_to. and another on reln_typ_id, , obj_id_owned_by, active_dt_from and active_dt_to. I'd appreciate any input the group can come up with. Tony

Multiple PostgreSQL instances on one machine

I am trying to consolidate some machines in my server room particularly in the testing environment and I was hoping someone could point me in the right direction. I currently have three machines running PostgreSQL for testing purposes. Each week a backup is made of the production database and this

partition table query allocate much memory

hi guys, I'm not sure it was a bug,but a query on partition table allocate nearly 100GB memory is not normal.pid :119775 as below [cid:part1.2A42CFAD.715BF014@outlook.com] SQL: SELECT DISTINCT etl_source,gid FROM zh_erp.buy2 WHERE gid NOT IN (SELECT gid FROM zh_erp.goodsh); I had modified th

Re: Rationale for aversion to the central database?

I have done some big contracts for large financial companies, and for most of them, ANY changes to the DB structure required extensive 3rd party testing and a change control process that sometimes took weeks. But we did get a waiver for the use of DB 'code' like stored procedures and views, which

How to perform PITR when all of the logs won't fit on the drive

Hello, I have a situation where something was deleted from a database that shouldn't have been so I am having to take a base backup and perform a point-in-time-recovery. The problem I have is that the decompressed WAL files will not fit on the drive of the machine I am trying to do the restore on.

copy error with json/jsonb types

hi dears, I'm using copy and jdbc copyin to build load data to pg,data type in table is jsonb.postgresql version are 9.6 and 10.0. Some records with escape characters would be failed with error"ERROR: invalid input syntax for type json" but when using insert they could be executed successful