Pgadmin4 apt packages.
Where are the pgadmin4 apt packages? I have set up the PPA as specified on the download page but I see no pgadmin4 packages in the list. I am running ubuntu 17-10 but I had to set up the zesty PPA because there are apt repos for artfful.
pg_pass and pg_service
can I refer to a pg_service entry in the pgpass file? It seems silly to repeat all the information in the pgpass just to add the password. Alternatively can I put the user password in the pg_service file?
Is there a way to dump schema to files on disk and keep them in sync
I want to dump my postgres schema to disk in neat directories like pgadmin presents. Then I want to be able to edit the files and sync changes to the database and ideally if changes were made in the database to sync them back to the disk. Is there a tool that does this? Is there a tool that will dump the schema into separate directories and files like pgadmin does? Thanks.
Re: Is there a way to dump schema to files on disk and keep them in sync
My primary use case for this is for development and experimentation, I have no intent on using it on production servers :) I normally use migrations for those. On Sun, Dec 13, 2020 at 9:04 PM Ron wrote: > > On 12/12/20 8:58 PM, Tim Uckun wrote: > > I want to dump my postgres schema to disk in neat directories like > > pgadmin presents. Then I want to be able to edit the files and sync > > changes to the database and ideally if changes were made in the > > database to sync them back to the disk. > > That could get really time- and disk-consuming if one of those "edits" was > to convert a column in a large 500M row table from numeric to text (or vice > versa), or add a column to the "middle" of a table. > > Tricky to program, too, given all the foreign keys, partitions, etc, etc > that can be part of a table. > > > Is there a tool that does this? Is there a tool that will dump the > > schema into separate directories and files like pgadmin does? > > -- > Angular momentum makes the world go 'round. > >
Re: Is there a way to dump schema to files on disk and keep them in sync
>pgAdmin does not create directories, it just organizes the contents of the system catalogs into GUI elements. I realize that :). I meant organized in the same way but on disk.
Indexes that would span partitions.
What's a good strategy for dealing with indexes (multiple) that would span multiple partitions of a table. For example: Let's say I have a table that's partitioned by group_id because I want all the records for a group to be in the same partition. Let's say I have a field called "expires_at" which is a date. Let's presume the expiry date can be updated. If I want to do something to every record that is due to be expired the database is going to be touching practically every partition and this seems like it could present a problem. Is there a more efficient way to deal with something like this?
Re: Indexes that would span partitions.
> How many partitions do you expect to have? Why are you partitioning on that > column? Do you have need to drop old data all at the same time? How many rows > in each partition or in total do you expect to have? In my case there is a hierarchy so the partitions are based on certain parts of the hierarchy. For example. There is an item 1.2.3.4.5.6.7.8 The partition is based on 1.2 (a function based hash scheme). I could fine tune the number of partitions simply by writing my function. Each item has certain attributes that I want to index. For example all items have an expiry_date. I will be archiving the items when they expire but of course all those items will not be in the same partition. In fact it's highly probable all partitions will have to be visited in order to accomplish this task. Unfortunately I will not be able to drop an entire partition and I can't partition by expiry date because access is most often by the item number. Also of course the expiry date can change over the lifetime of the item.
Resources on modeling ordered hierachies?
There a tons of articles about how to model hierarchies in SQL but I haven't seen any about dealing with hierarchies where the order of children is important. The canonical example is a simple outline 1. 1.1 1.1.1 1.2 2. 2.1 etc If I am doing an insert where parent is 1.1 it should name it 1.1.2 which to me means doing something like select max(id) + 1 where parent = 1.1 or something like that which might turn out to be expensive. Similarly if I want to insert something between 1.1 and 1.2 I need to do something like update id set id = id+1 where parent = 1 and id >1 Items could of course be "promoted" this way or moved up and down the hierarchy to be below or above their peers. Does anybody know of a more elegant way to do this?
Long living and expiring locks?
I have multiple processes connecting to the same database. In order to prevent race conditions the I would like the process to issue a SELECT FOR UPDATE to lock the records that need to be processed. The problem is that the locks are not inside of a single translation. The process is multi step. Records are selected and presented to the user, the user does some stuff and submits a form, the submitted form updates those records and frees the lock. Ideally I would do a select for update when the form is shown to the user but with an expiry time so that if the user decides to move on and not submit any changes the locks will expire. I could do this with a locked_at field that I can update on select but I would prefer to let postgres handle the locks if possible.
Using functions in regexp replace captures
I want to do something like this
SELECT REGEXP_REPLACE('some_string','(.*)
(.*)',some_function_that_returns_string('\2',' \1'));
Is this possible at all?
Choosing an index on partitioned tables.
I have a series of tables which are going to be queries mostly on two columns. A timestamp table and a metric type column. My plan is to partition by date ranges which means the primary key has to include the timestamp column and the id column As far as I know there is no way to specify an index type for those columns. The metric type is a text column and will not be very selective. It will have somewhere around 200 types of metrics and they will all be short, less than ten characters. Given that there will be a lot of records I was wondering what type of index would be ideal for that column. Seems like hash indexes would be ideal because only comparison will be = and they are smaller than Btrees but for a while they were not recommended. Would hash be the best or would something work better?
Re: Choosing an index on partitioned tables.
Hi Brent. I looked at timescaledb. It does make partitioning on date ranges automatic which is awesome and as you said it does add a couple of extra features on top of postgres but their cloud offering are much more expensive than buying a generic postgres instance from AWS. A generic t3.medium on amazon costs ~90 dollars per month in singapore and ~140 per month from timescale. Is it really worth the extra expense? On Tue, Sep 7, 2021 at 4:06 PM Brent Wood wrote: > Hi Tim, > > I've had good success with TimescaleDB for large timesries databases (40b > readings). > https://www.timescale.com/ > > You turn your timestamp table into a Timescale hypertable and it looks > after the indexing and partitioning automatically, with the table accessed > like a normal postgres table, but very quickly. > > It also adds some SQL functions to add a bit of time based query > functionality. > > > Cheers > > Brent Wood > > Principal Technician, Fisheries > NIWA > DDI: +64 (4) 3860529 > > -- > *From:* Tim Uckun > *Sent:* Tuesday, September 7, 2021 15:44 > *To:* pgsql-general > *Subject:* Choosing an index on partitioned tables. > > I have a series of tables which are going to be queries mostly on two > columns. A timestamp table and a metric type column. > > My plan is to partition by date ranges which means the primary key has > to include the timestamp column and the id column As far as I know > there is no way to specify an index type for those columns. > > The metric type is a text column and will not be very selective. It > will have somewhere around 200 types of metrics and they will all be > short, less than ten characters. > > Given that there will be a lot of records I was wondering what type of > index would be ideal for that column. Seems like hash indexes would be > ideal because only comparison will be = and they are smaller than > Btrees but for a while they were not recommended. > > Would hash be the best or would something work better? > > > <https://www.niwa.co.nz> > Brent Wood > Principal Technician - GIS and Spatial Data Management > Programme Leader - Environmental Information Delivery > +64-4-386-0529 > > National Institute of Water & Atmospheric Research Ltd (NIWA) > 301 Evans Bay Parade Hataitai Wellington New Zealand > *Connect with NIWA:* niwa.co.nz <https://www.niwa.co.nz> Facebook > <https://www.facebook.com/nzniwa> LinkedIn > <https://www.linkedin.com/company/niwa> Twitter > <https://twitter.com/niwa_nz> Instagram > <https://www.instagram.com/niwa_science> To ensure compliance with legal > requirements and to maintain cyber security standards, NIWA's IT systems > are subject to ongoing monitoring, activity logging and auditing. This > monitoring and auditing service may be provided by third parties. Such > third parties can access information transmitted to, processed by and > stored on NIWA's IT systems >
Re: Choosing an index on partitioned tables.
Thanks! That's great about the Btree deduplication feature in 13. On Tue, Sep 7, 2021 at 7:21 PM Laurenz Albe wrote: > > On Tue, 2021-09-07 at 15:44 +1200, Tim Uckun wrote: > > I have a series of tables which are going to be queries mostly on two > > columns. A timestamp table and a metric type column. > > > > My plan is to partition by date ranges which means the primary key has > > to include the timestamp column and the id column As far as I know > > there is no way to specify an index type for those columns. > > > > The metric type is a text column and will not be very selective. It > > will have somewhere around 200 types of metrics and they will all be > > short, less than ten characters. > > > > Given that there will be a lot of records I was wondering what type of > > index would be ideal for that column. Seems like hash indexes would be > > ideal because only comparison will be = and they are smaller than > > Btrees but for a while they were not recommended. > > > > Would hash be the best or would something work better? > > If you don't need to speed up searches by "id", you could define > the primary key on (timestamp_col, id), which can be used to speed > up searches by the timestamp column without defining an extra index. > > I would choose a B-tree index for the metrics column. > With the B-tree deduplication feature added in v13, the index will > be small, and I doubt that hash indexes would perform much better. > > If there is a dominant value, you could consider a partial index > that excludes that value. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
Re: Choosing an index on partitioned tables.
To be fair Timescale also adds some other features which might be useful. For example they add some SQL enhancements like last value and auto maintaining materialized views and such. The automatic management of partitions is also pretty big plus in my opinion. You can get some of the equivalent functionality by writing stored procs and deploying pg_cron but it's nice to have those things built in. It's open source so you can just deploy their docker image which I did for development but in the end I wanted to try and do the same thing in plain jane postgres. On Tue, Sep 7, 2021 at 7:24 PM Laurenz Albe wrote: > > On Tue, 2021-09-07 at 04:06 +, Brent Wood wrote: > > From: Tim Uckun > > > My plan is to partition by date ranges which means the primary key has > > > to include the timestamp column and the id column As far as I know > > > there is no way to specify an index type for those columns. > > > > > > The metric type is a text column and will not be very selective. It > > > will have somewhere around 200 types of metrics and they will all be > > > short, less than ten characters. > > > > > > Given that there will be a lot of records I was wondering what type of > > > index would be ideal for that column. Seems like hash indexes would be > > > ideal because only comparison will be = and they are smaller than > > > Btrees but for a while they were not recommended. > > > > > > Would hash be the best or would something work better? > > > > I've had good success with TimescaleDB for large timesries databases (40b > > readings). > > That has nothing to do with indexing, and I would think twice to install > an invasive extension like that and add a dependency on third-party code, > just because I want to partition a table. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
Re: Choosing an index on partitioned tables.
It's weird that it's supported on AWS and many other providers but not in the official docker images. On Tue, Sep 7, 2021 at 9:16 PM Magnus Hagander wrote: > > On Tue, Sep 7, 2021 at 10:51 AM Tim Uckun wrote: > > > > To be fair Timescale also adds some other features which might be > > useful. For example they add some SQL enhancements like last value > > and auto maintaining materialized views and such. The automatic > > management of partitions is also pretty big plus in my opinion. You > > can get some of the equivalent functionality by writing stored procs > > and deploying pg_cron but it's nice to have those things built in. > > If you want automatic partition management, look at pg_partman. No > need to write your own procs and deploy with cron. > > And FWIW, in reference to the discussions about AWS, it is supported on RDS. > > -- > Magnus Hagander > Me: https://www.hagander.net/ > Work: https://www.redpill-linpro.com/
Re: Choosing an index on partitioned tables.
There is an image marked as official: https://hub.docker.com/_/postgres I presumed this was maintained by the postgres team. On Tue, Sep 7, 2021 at 9:59 PM Magnus Hagander wrote: > > On Tue, Sep 7, 2021 at 11:52 AM Tim Uckun wrote: > > > > It's weird that it's supported on AWS and many other providers but not > > in the official docker images. > > That'd be something to talk to the docker people about I guess? There > are no official docker images published by *postgresql*. (And of > course, AWS or Azure or whomever do whatever they want, but I assume > they're including pg_partman because it's a very popular extension) > > -- > Magnus Hagander > Me: https://www.hagander.net/ > Work: https://www.redpill-linpro.com/
ALTER DATABASE SET not working as expected?
This seems simple enough but it's not working.
alter database dbname set "blah.blah" = 'test_value' ;
The above statement returns with no errors.
select current_setting('blah.blah')
The above statement returns a record with null
Also how long does the "ALTER DATABASE SET" last? Will it survive a
shutdown and restart?
Re: ALTER DATABASE SET not working as expected?
Ok thanks.
On Thu, Sep 9, 2021 at 9:06 PM Ian Lawrence Barwick wrote:
>
> 2021年9月9日(木) 16:53 Tim Uckun :
> >
> > This seems simple enough but it's not working.
> >
> > alter database dbname set "blah.blah" = 'test_value' ;
> >
> > The above statement returns with no errors.
> >
> > select current_setting('blah.blah')
> >
> > The above statement returns a record with null
>
> Open a new database session and you should see the setting.
>
> > Also how long does the "ALTER DATABASE SET" last? Will it survive a
> > shutdown and restart?
>
> Yes.
>
>
> Regards
>
> Ian Barwick
>
> --
> EnterpriseDB: https://www.enterprisedb.com
Timestamp with vs without time zone.
I am hoping to get some clarification on timestamp with time zone. My understanding is that timestamp with time zone stores data in UTC but displays it in your time zone. Does this also work on queries? If I query between noon and 2:00 PM on some date in time zone XYZ does pg translate the query to UTC before sending it to the server? To provide context I have the following situation. I have a data file to import. All the dates in the time zone pacific/auckland. My app reads the data , does some processing and cleaning up and then saves it to the database. The language I am using creates the time data type with the right time zone. The processing is being done on a server which is on UTC, the database server is also on UTC. I am pretty sure the ORM isn't appending "at time zone pacific/Auckland" to the data when it appends it to the database. So does the database know the timestamp is in auckland time when the client is connecting from a server on UTC?
Re: Timestamp with vs without time zone.
It seems like it would be so much more useful if the timestamp with time zone type actually stored the time zone in the record. On Tue, Sep 21, 2021 at 7:25 PM Laurenz Albe wrote: > > On Tue, 2021-09-21 at 18:00 +1200, Tim Uckun wrote: > > I am hoping to get some clarification on timestamp with time zone. > > > > My understanding is that timestamp with time zone stores data in UTC > > but displays it in your time zone. > > That is correct. > When a timestamp is rendered as string, it it shown in the time zone > specified by the current setting of the "timezone" parameter in your > database session. > > > Does this also work on queries? If > > I query between noon and 2:00 PM on some date in time zone XYZ does pg > > translate the query to UTC before sending it to the server? > > Yes. > > > To provide context I have the following situation. > > > > I have a data file to import. All the dates in the time zone > > pacific/auckland. My app reads the data , does some processing and > > cleaning up and then saves it to the database. > > > > The language I am using creates the time data type with the right time > > zone. The processing is being done on a server which is on UTC, the > > database server is also on UTC. I am pretty sure the ORM isn't > > appending "at time zone pacific/Auckland" to the data when it appends > > it to the database. > > > > So does the database know the timestamp is in auckland time when the > > client is connecting from a server on UTC? > > It does, but only if you set "timezone" appropriately in the database > session. You could use ALTER ROLE to change the default setting for a > database user, but it might be best to set that from the application. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
Re: Re: Timestamp with vs without time zone.
One specified by the user. Many date formats carry either an offset or the time zone information. On Tue, Sep 21, 2021 at 7:39 PM Karsten Hilbert wrote: > > > It seems like it would be so much more useful if the timestamp with > > time zone type actually stored the time zone in the record. > > Which one ? > > Karsten >
Re: Re: Re: Timestamp with vs without time zone.
Yes it would record the timestamp and then also record the time zone. That way all the conversion functions would still work. That's the way it works in the programming languages I use anyway. On Tue, Sep 21, 2021 at 8:09 PM Karsten Hilbert wrote: > > > > > It seems like it would be so much more useful if the timestamp with > > > > time zone type actually stored the time zone in the record. > > > > > > Which one ? > > > > > One specified by the user. Many date formats carry either an offset > > or the time zone information. > > What would that TZ mean, exactly, especially in relation to the timestamp > itself ? > > Would the timestamp be stored as that TZ ? > > Karsten
Re: Re: Timestamp with vs without time zone.
That's all true and I won't argue about the madness that is timezones in the world. I am simply thinking it would be some sort of a struct like thing which would store the numerical value of the time stamp and also the time zone that time was recorded in. Presumably everything else is an insane calculation from there. What was the offset on that day? I guess it depends on the daylight savings time. What would the conversion to another time zone be? That would depend on the DST settings on that day in both places. Mankind can't agree on what side of the road to drive on, what the electrical voltage should be at the wall, what those plugs should be, how you should charge your phone or anything else for that matter so there is no way of avoiding the insanity. It's just that the phrase "timestamp with time zone" would seem to indicate the time zone is stored somewhere in there. On Tue, Sep 21, 2021 at 8:44 PM Peter J. Holzer wrote: > > On 2021-09-21 09:39:59 +0200, Karsten Hilbert wrote: > > > It seems like it would be so much more useful if the timestamp with > > > time zone type actually stored the time zone in the record. > > > > Which one ? > > To expand on that question a bit: > > There are several formats to specify a time zone: By offset, by name > (several nomenclatures), etc. > > For example, Karsten's mail had a timestamp of "2021-09-21 09:39:59 > +0200". Thst's enough information to convert it to UTC, but not enough > for date arithmetic. For example what is > '2021-09-21 09:39:59 +0200' + '2 months'::interval? > > Should the result be '2021-11-21 09:39:59 +0200' or '2021-11-21 09:39:59 > +0100'? I'm guessing that Karsten is in Germany, so it's probably the > latter. But to compute that you need to know that the timezone is > Europe/Berlin (or at least CET). Even that is not enough for dates in > the more distant future. The EU has decided to abolish DST (that should > have happened in 2020, but of course there was that little problem that > got in the way), but we don't know when that will happen and which > timezone Germany will choose. So for a date in e.g. 2025 we simply don't > know what the timezone offset will be. > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!"
Re: Timestamp with vs without time zone.
I'll add another layer of complication. You have a database server hosted in Australia, and that's also where your web server and api server is. You have customers all over the world though so you set up additional API servers in Europe, USA, Japan etc. A korean user will fetch you single page app as static HTML from S3 with cloudfront. It will hit your japanese API server, which will fetch the data from your japanese read only replica with the master being in Australia. The master DB writes the records has to know your end user is in Korea somehow so you have to carry that time zone all the way across those tiers. To me the ideal solution would be to have a compound object which has the time zone in it. This object gets passed through the tiers and end up at the database where it's stored. On Wed, Sep 22, 2021 at 1:52 PM Tim Cross wrote: > > > FWS Neil writes: > > > On Sep 21, 2021, at 12:34 PM, Dave Cramer > > wrote: > > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > > > > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > > That's all true and I won't argue about the madness that is timezones > > > in the world. I am simply thinking it would be some sort of a struct > > > like thing which would store the numerical value of the time stamp and > > > also the time zone that time was recorded in. Presumably everything > > > else is an insane calculation from there. What was the offset on that > > > day? I guess it depends on the daylight savings time. What would the > > > conversion to another time zone be? That would depend on the DST > > > settings on that day in both places. > > > > Yes, but HOW IS THAT TIME ZONE STORED? > > > > As a user you can say "I don't care, just make it work somehow". > > > > But as a developer you have to decide on a specific way. And as a > > database developer in particular you would have to choose a way which > > works for almost everybody. > > > > And that's the problem because ... > > > > > Mankind can't agree on what side of the road to drive on, what the > > > electrical voltage should be at the wall, what those plugs should be, > > > how you should charge your phone or anything else for that matter > > > > ... people have different needs and it would be difficult to satisfy > > them all. > > > > Simply storing an offset from UTC is simple, fast, doesn't take much > > space - but it would be almost as misleading as the current state. A > > simple offset is not a time zone. > > > > Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an > > identifier for what most people think of as a time zone - but that takes > > a lot of space, it needs a lookup for almost any operation and worst of > > all, you couldn't index such a column (at least not with a btree index) > > because the comparison functions aren't stable. > > > > You could use a numeric indentifier instead of the name, that would take > > less space but wouldn't solve the other problems (and add the problem > > that now you have just added another mapping which you need to maintain). > > > > There are other ways, but I'm sure they all have some pros and some > > cons. None will be perfect. > > > > So I don't think there is an obvious (or even non-obvious, but clearly > > good) way for the PostgreSQL developers to add a real "timestamp with > > timezone" type. > > > > As an application developer however, you can define a compound type (or > > just use two or three columns together) which satisfies the needs of > > your specific application. > > > > > It's just that the phrase "timestamp with time zone" would seem to > > > indicate the time zone is stored somewhere in there. > > > > I absolutely agree. Calling a type which doesn't include a timezone > > "timestamp with timezone" is - how do I put this? - more than just > > weird. "timestamp without timezone" should be called "local timestamp > > with unspecified timezone" and "timestamp with timezone" should be > > called "global timestamp without timezone". However, those aren't SQL > > names. > > > > I would say this is a perspective thing. It's a timestamp with a time zone > > from the client's perspective. > > > > A timestamp cannot have a time zone and be a valid timestamp. > > > > Let me explain. > > >
