Disk Groups/Storage Management for a Large Database in PostgreSQL
Hi, We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large database of 15TB-20TB. I would like to know from the experts that is it a good idea to create LVMs to manage storage for the database? Or are there any other better options/tools for disk groups in PostgreSQL, similar to ASM in Oracle? Thanks Amit
Re: unbale to list schema
On 1/17/24 12:46 PM, Atul Kumar wrote: Hi, I am not able to find any solution to list all schemas in all databases at once, to check the structure of the whole cluster. Easiest way to do this is `pg_dumpall --schema-only`. -- Jim Nasby, Data Architect, Austin TX
Re: Mimic ALIAS in Postgresql?
On 1/16/24 6:41 PM, Rob Sargent wrote: On 1/16/24 17:39, Jim Nasby wrote: On 1/16/24 4:57 PM, Rob Sargent wrote: Or perhaps you have to beef the sed up to use word boundaries just in case. I'm not a Java web developer... 😁 You need to adjust you glasses if that's what you see me as. Reality is that basically all modern (as in last 20 years) SQL access is via frameworks that all use their own language and come up with SQL based on that. How hard it'd be to bulk change the schema depends entirely on the framework. Hm, it's a string /somewhere/. The rest of this thread might be accused of adding to the problem. No, it's not, at least not as a complete SQL statement. See [1] as an example of how this works in Ruby on Rails. Most modern frameworks work in a similar fashion: you DON'T write raw SQL, or anything that looks anything like it. In fact, many (most?) of these frameworks make it difficult to do anything in raw SQL because it completely breaks the paradigm of the framework. Note that I'm talking about *frameworks*, not languages. But since most languages require huge amounts of boilerplate to create a web service or website it's not surprising that pretty much everyone uses frameworks. (Go is actually an interesting exception to this.) 1: https://guides.rubyonrails.org/active_record_querying.html#find -- Jim Nasby, Data Architect, Austin TX
Re: unbale to list schema
On Wed, Jan 17, 2024 at 1:46 PM Atul Kumar wrote:
> Hi,
>
> I am not able to find any solution to list all schemas in all databases at
> once, to check the structure of the whole cluster.
>
> As I need to give a few privileges to a user to all databases, their
> schemas and schemas' objects (tables sequences etc.).
>
> Please let me know if there is any solution/ query that will serve the
> purpose.
>
Is this what you are looking for?
#!/bin/bash
declare DbHost=
declare DB=
declare Schemas="select schema_name from information_schema.schemata
where schema_name not like 'pg_%' and schema_name !=
'information_schema';"
for s in $(psql --host=$DbHost --dbname=$DB -AXtc "${Schemas}")
do
pg_dump --dbname=$DB --schema-only --schema=${s} >
schema_${DbHost}_${DB}_${s}.sql
done
Re: Mimic ALIAS in Postgresql?
On 1/17/24 16:25, Jim Nasby wrote: On 1/16/24 6:41 PM, Rob Sargent wrote: On 1/16/24 17:39, Jim Nasby wrote: On 1/16/24 4:57 PM, Rob Sargent wrote: Or perhaps you have to beef the sed up to use word boundaries just in case. I'm not a Java web developer... 😁 You need to adjust you glasses if that's what you see me as. Reality is that basically all modern (as in last 20 years) SQL access is via frameworks that all use their own language and come up with SQL based on that. How hard it'd be to bulk change the schema depends entirely on the framework. Hm, it's a string /somewhere/. The rest of this thread might be accused of adding to the problem. No, it's not, at least not as a complete SQL statement. See [1] as an example of how this works in Ruby on Rails. Most modern frameworks work in a similar fashion: you DON'T write raw SQL, or anything that looks anything like it. In fact, many (most?) of these frameworks make it difficult to do anything in raw SQL because it completely breaks the paradigm of the framework. Note that I'm talking about *frameworks*, not languages. But since most languages require huge amounts of boilerplate to create a web service or website it's not surprising that pretty much everyone uses frameworks. (Go is actually an interesting exception to this.) 1: https://guides.rubyonrails.org/active_record_querying.html#find You may well be correct, but I have to ask the OP (Ron) if this is the case in the current situation. I find it difficult to conceive of a "framework" apparently arbitrarily flipping between the alias and the base name. (I read "For example, sometimes" as arbitrarily.) The few database frameworks with which I'm familiar would tolerate the coder using either name. And indeed in those (hibernate, mybatis, jOOQ) the coder would be the one choosing the /nom du jour/.
Re: Mimic ALIAS in Postgresql?
On Mon, Jan 22, 2024 at 6:40 PM Rob Sargent wrote: > On 1/17/24 16:25, Jim Nasby wrote: > > On 1/16/24 6:41 PM, Rob Sargent wrote: > > On 1/16/24 17:39, Jim Nasby wrote: > > On 1/16/24 4:57 PM, Rob Sargent wrote: > > Or perhaps you have to beef the sed up to use word boundaries just > in case. > > > I'm not a Java web developer... 😁 > > > You need to adjust you glasses if that's what you see me as. > > > Reality is that basically all modern (as in last 20 years) SQL access is > via frameworks that all use their own language and come up with SQL based > on that. How hard it'd be to bulk change the schema depends entirely on the > framework. > > Hm, it's a string /somewhere/. The rest of this thread might be accused > of adding to the problem. > > > No, it's not, at least not as a complete SQL statement. See [1] as an > example of how this works in Ruby on Rails. Most modern frameworks work in > a similar fashion: you DON'T write raw SQL, or anything that looks anything > like it. In fact, many (most?) of these frameworks make it difficult to do > anything in raw SQL because it completely breaks the paradigm of the > framework. > > Note that I'm talking about *frameworks*, not languages. But since most > languages require huge amounts of boilerplate to create a web service or > website it's not surprising that pretty much everyone uses frameworks. (Go > is actually an interesting exception to this.) > > 1: https://guides.rubyonrails.org/active_record_querying.html#find > > You may well be correct, but I have to ask the OP (Ron) if this is the > case in the current situation. I find it difficult to conceive of a > "framework" apparently arbitrarily flipping between the alias and the base > name. (I read "For example, sometimes" as arbitrarily.) The few database > frameworks with which I'm familiar would tolerate the coder using either > name. And indeed in those (hibernate, mybatis, jOOQ) the coder would be > the one choosing the /nom du jour/. > I don't know what, if any, framework the developer uses.
Backup certain months old data
Hello! I would like to know how we can backup certain months old data from PgSQL and then delete it. The intent is to backup everything that is older than 2 quarters to a blob storage and delete it, to improve performance and reduce billing. Regards Siraj
Re: Backup certain months old data
On Mon, Jan 22, 2024 at 10:12 PM Siraj G wrote: > Hello! > > I would like to know how we can backup certain months old data from PgSQL > Use the COPY command. > and then delete it. > That would depend on how many records, how big the records are, and if there's index support on the "date" field. > The intent is to backup everything that is older than 2 quarters to a blob > storage and delete it, to improve performance and reduce billing. > I had to do something similar for my previous employer. 1. Used COPY to dump the old data. 2. CREATE INDEX i_foo_sd1 ON foo (some_date); 3. DELETE FROM foo WHERE some_date BETWEEN x AND y; When there wasn't a lot of data, it was the whole month. When there was a lot of data, I looped through it one day at a time.. 4. DROP INDEX i_foo_sd1; It was a bash script that reads a text file, where each row is a tab-delimited record with table name and column,
Re: Backup certain months old data
On 1/22/24 19:11, Siraj G wrote: Hello! I would like to know how we can backup certain months old data from PgSQL and then delete it. The intent is to backup everything that is older than 2 quarters to a blob storage and delete it, to improve performance and reduce billing. 1) Postgres does not track the insert/update times of data, so unless you have fields that track that you will not be able to do that. 2) If you do have a way of telling the times for all the records, are you sure that removing the data on a time basis will be clean enough operation that it will not leave the data in a compromised state? 3) Have you considered partitioning? See here: https://www.postgresql.org/docs/current/ddl-partitioning.html Regards Siraj -- Adrian Klaver adrian.kla...@aklaver.com
Re: Disk Groups/Storage Management for a Large Database in PostgreSQL
Hi Amit, El lun, 22 ene 2024 18:44, Amit Sharma escribió: > Hi, > > We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large > database of 15TB-20TB. > > I would like to know from the experts that is it a good idea to create > LVMs to manage storage for the database? > > Or are there any other better options/tools for disk groups in PostgreSQL, > similar to ASM in Oracle? > > Thanks > Amit > Simple question that requires a somewhat more complex answer. There are actually 3 metrics to consider: 1) Capacity Your database doesn't fit on a single disk, so you need to distribute your data across several disks. LVM would indeed be an option (as well as ZFS or RAID disk arrays) 2) Safety If you loose 1 disk, your data is at risk, as you're likely to loose all tables partially loaded on that disk. LVM is still an option as long as it is configured on a RAID array. ZFS can do that natively. 3) Performance Oracle ADM ensures performance by automatically controlling the distribution of the tables. I would need to see on a real case how it is actually done. For sure, LVM and ZFS won't have this type of granularity. On the other hand, you can distribute your data in table partitions to help this distribution. It is not automatic but will surely help you to distribute your workload. Hope it helps Olivier >
