public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 30/33] Alphabetize pg_ls_{tmpdir,archivestatusdir}
4+ messages / 4 participants
[nested] [flat]
* [PATCH 30/33] Alphabetize pg_ls_{tmpdir,archivestatusdir}
@ 2022-01-19 22:38 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2022-01-19 22:38 UTC (permalink / raw)
since: 9cd92d1a33699f86aa53d44ab04cc3eb50c18d1 c48101620151f78ee184e5f81766cbac347d9a11
Could backpatch to v11?
---
doc/src/sgml/func.sgml | 76 +++++++++++++++++++++---------------------
1 file changed, 38 insertions(+), 38 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 90169a0ce72..29a596233a0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27306,6 +27306,31 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
</thead>
<tbody>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_ls_archive_statusdir</primary>
+ </indexterm>
+ <function>pg_ls_archive_statusdir</function> ()
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>name</parameter> <type>text</type>,
+ <parameter>size</parameter> <type>bigint</type>,
+ <parameter>modification</parameter> <type>timestamp with time zone</type> )
+ </para>
+ <para>
+ Returns the name, size, and last modification time (mtime) of each
+ ordinary file in the server's WAL archive status directory
+ (<filename>pg_wal/archive_status</filename>). Filenames beginning
+ with a dot, directories, and other special files are excluded.
+ </para>
+ <para>
+ This function is restricted to superusers and members of
+ the <literal>pg_monitor</literal> role by default, but other users can
+ be granted EXECUTE to run the function.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -27356,33 +27381,9 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
- <primary>pg_ls_waldir</primary>
- </indexterm>
- <function>pg_ls_waldir</function> ()
- <returnvalue>setof record</returnvalue>
- ( <parameter>name</parameter> <type>text</type>,
- <parameter>size</parameter> <type>bigint</type>,
- <parameter>modification</parameter> <type>timestamp with time zone</type> )
- </para>
- <para>
- Returns the name, size, and last modification time (mtime) of each
- ordinary file in the server's write-ahead log (WAL) directory.
- Filenames beginning with a dot, directories, and other special files
- are excluded.
- </para>
- <para>
- This function is restricted to superusers and members of
- the <literal>pg_monitor</literal> role by default, but other users can
- be granted EXECUTE to run the function.
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>pg_ls_archive_statusdir</primary>
+ <primary>pg_ls_tmpdir</primary>
</indexterm>
- <function>pg_ls_archive_statusdir</function> ()
+ <function>pg_ls_tmpdir</function> ( <optional> <parameter>tablespace</parameter> <type>oid</type> </optional> )
<returnvalue>setof record</returnvalue>
( <parameter>name</parameter> <type>text</type>,
<parameter>size</parameter> <type>bigint</type>,
@@ -27390,9 +27391,12 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
</para>
<para>
Returns the name, size, and last modification time (mtime) of each
- ordinary file in the server's WAL archive status directory
- (<filename>pg_wal/archive_status</filename>). Filenames beginning
- with a dot, directories, and other special files are excluded.
+ ordinary file in the temporary file directory for the
+ specified <parameter>tablespace</parameter>.
+ If <parameter>tablespace</parameter> is not provided,
+ the <literal>pg_default</literal> tablespace is examined. Filenames
+ beginning with a dot, directories, and other special files are
+ excluded.
</para>
<para>
This function is restricted to superusers and members of
@@ -27403,11 +27407,10 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
<row>
<entry role="func_table_entry"><para role="func_signature">
-
<indexterm>
- <primary>pg_ls_tmpdir</primary>
+ <primary>pg_ls_waldir</primary>
</indexterm>
- <function>pg_ls_tmpdir</function> ( <optional> <parameter>tablespace</parameter> <type>oid</type> </optional> )
+ <function>pg_ls_waldir</function> ()
<returnvalue>setof record</returnvalue>
( <parameter>name</parameter> <type>text</type>,
<parameter>size</parameter> <type>bigint</type>,
@@ -27415,12 +27418,9 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
</para>
<para>
Returns the name, size, and last modification time (mtime) of each
- ordinary file in the temporary file directory for the
- specified <parameter>tablespace</parameter>.
- If <parameter>tablespace</parameter> is not provided,
- the <literal>pg_default</literal> tablespace is examined. Filenames
- beginning with a dot, directories, and other special files are
- excluded.
+ ordinary file in the server's write-ahead log (WAL) directory.
+ Filenames beginning with a dot, directories, and other special files
+ are excluded.
</para>
<para>
This function is restricted to superusers and members of
--
2.17.1
--924gEkU1VlJlwnwX
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0031-Alphabetize-pg_ls_logical-functions.patch"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 20:53 Thomas Simpson <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Thomas Simpson @ 2024-07-18 20:53 UTC (permalink / raw)
To: [email protected]
On 18-Jul-2024 16:32, Ron Johnson wrote:
> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <[email protected]> wrote:
> [snip]
>
> [BTW, v9.6 which I know is old but this server is stuck there]
>
> [snip]
>
> I know I'm stuck with the slow rebuild at this point. However, I
> doubt I am the only person in the world that needs to dump and
> reload a large database. My thought is this is a weak point for
> PostgreSQL so it makes sense to consider ways to improve the dump
> reload process, especially as it's the last-resort upgrade path
> recommended in the upgrade guide and the general fail-safe route
> to get out of trouble.
>
> No database does fast single-threaded backups.
Agreed. My thought is that is should be possible for a 'new dumpall' to
be multi-threaded.
Something like :
* Set number of threads on 'source' (perhaps by querying a listening
destination for how many threads it is prepared to accept via a control
port)
* Select each database in turn
* Organize the tables which do not have references themselves
* Send each table separately in each thread (or queue them until a
thread is available) ('Stage 1')
* Rendezvous stage 1 completion (pause sending, wait until feedback from
destination confirming all completed) so we have a known consistent
state that is safe to proceed to subsequent tables
* Work through tables that do refer to the previously sent in the same
way (since the tables they reference exist and have their data) ('Stage 2')
* Repeat progressively until all tables are done ('Stage 3', 4 etc. as
necessary)
The current dumpall is essentially doing this table organization
currently [minus stage checkpoints/multi-thread] otherwise the dump/load
would not work. It may even be doing a lot of this for 'directory'
mode? The change here is organizing n threads to process them
concurrently where possible and coordinating the pipes so they only send
data which can be accepted.
The destination would need to have a multi-thread listen and co-ordinate
with the sender on some control channel so feed back completion of each
stage.
Something like a destination host and control channel port to establish
the pipes and create additional netcat pipes on incremental ports above
the control port for each thread used.
Dumpall seems like it could be a reasonable start point since it is
already doing the complicated bits of serializing the dump data so it
can be consistently loaded.
Probably not really an admin question at this point, more a feature
enhancement.
Is there anything fundamentally wrong that someone with more intimate
knowledge of dumpall could point out?
Thanks
Tom
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-18 22:41 Ron Johnson <[email protected]>
parent: Thomas Simpson <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Ron Johnson @ 2024-07-18 22:41 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Multi-threaded writing to the same giant text file won't work too well,
when all the data for one table needs to be together.
Just temporarily add another disk for backups.
On Thu, Jul 18, 2024 at 4:55 PM Thomas Simpson <[email protected]> wrote:
>
> On 18-Jul-2024 16:32, Ron Johnson wrote:
>
> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <[email protected]> wrote:
> [snip]
>
>> [BTW, v9.6 which I know is old but this server is stuck there]
>>
> [snip]
>
>> I know I'm stuck with the slow rebuild at this point. However, I doubt I
>> am the only person in the world that needs to dump and reload a large
>> database. My thought is this is a weak point for PostgreSQL so it makes
>> sense to consider ways to improve the dump reload process, especially as
>> it's the last-resort upgrade path recommended in the upgrade guide and the
>> general fail-safe route to get out of trouble.
>>
> No database does fast single-threaded backups.
>
> Agreed. My thought is that is should be possible for a 'new dumpall' to
> be multi-threaded.
>
> Something like :
>
> * Set number of threads on 'source' (perhaps by querying a listening
> destination for how many threads it is prepared to accept via a control
> port)
>
> * Select each database in turn
>
> * Organize the tables which do not have references themselves
>
> * Send each table separately in each thread (or queue them until a thread
> is available) ('Stage 1')
>
> * Rendezvous stage 1 completion (pause sending, wait until feedback from
> destination confirming all completed) so we have a known consistent state
> that is safe to proceed to subsequent tables
>
> * Work through tables that do refer to the previously sent in the same way
> (since the tables they reference exist and have their data) ('Stage 2')
>
> * Repeat progressively until all tables are done ('Stage 3', 4 etc. as
> necessary)
>
> The current dumpall is essentially doing this table organization currently
> [minus stage checkpoints/multi-thread] otherwise the dump/load would not
> work. It may even be doing a lot of this for 'directory' mode? The change
> here is organizing n threads to process them concurrently where possible
> and coordinating the pipes so they only send data which can be accepted.
>
> The destination would need to have a multi-thread listen and co-ordinate
> with the sender on some control channel so feed back completion of each
> stage.
>
> Something like a destination host and control channel port to establish
> the pipes and create additional netcat pipes on incremental ports above the
> control port for each thread used.
>
> Dumpall seems like it could be a reasonable start point since it is
> already doing the complicated bits of serializing the dump data so it can
> be consistently loaded.
>
> Probably not really an admin question at this point, more a feature
> enhancement.
>
> Is there anything fundamentally wrong that someone with more intimate
> knowledge of dumpall could point out?
>
> Thanks
>
> Tom
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: filesystem full during vacuum - space recovery issues
@ 2024-07-19 02:59 Scott Ribe <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Scott Ribe @ 2024-07-19 02:59 UTC (permalink / raw)
To: Thomas Simpson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
1) Add new disk, use a new tablespace to move some big tables to it, to get back up and running
2) Replica server provisioned sufficiently for the db, pg_basebackup to it
3) Get streaming replication working
4) Switch over to new server
In other words, if you don't want terrible downtime, you need yet another server fully provisioned to be able to run your db.
--
Scott Ribe
[email protected]
https://www.linkedin.com/in/scottribe/
> On Jul 18, 2024, at 4:41 PM, Ron Johnson <[email protected]> wrote:
>
> Multi-threaded writing to the same giant text file won't work too well, when all the data for one table needs to be together.
>
> Just temporarily add another disk for backups.
>
> On Thu, Jul 18, 2024 at 4:55 PM Thomas Simpson <[email protected]> wrote:
>
> On 18-Jul-2024 16:32, Ron Johnson wrote:
>> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <[email protected]> wrote:
>> [snip]
>> [BTW, v9.6 which I know is old but this server is stuck there]
>> [snip]
>> I know I'm stuck with the slow rebuild at this point. However, I doubt I am the only person in the world that needs to dump and reload a large database. My thought is this is a weak point for PostgreSQL so it makes sense to consider ways to improve the dump reload process, especially as it's the last-resort upgrade path recommended in the upgrade guide and the general fail-safe route to get out of trouble.
>> No database does fast single-threaded backups.
> Agreed. My thought is that is should be possible for a 'new dumpall' to be multi-threaded.
> Something like :
> * Set number of threads on 'source' (perhaps by querying a listening destination for how many threads it is prepared to accept via a control port)
> * Select each database in turn
> * Organize the tables which do not have references themselves
> * Send each table separately in each thread (or queue them until a thread is available) ('Stage 1')
> * Rendezvous stage 1 completion (pause sending, wait until feedback from destination confirming all completed) so we have a known consistent state that is safe to proceed to subsequent tables
> * Work through tables that do refer to the previously sent in the same way (since the tables they reference exist and have their data) ('Stage 2')
> * Repeat progressively until all tables are done ('Stage 3', 4 etc. as necessary)
> The current dumpall is essentially doing this table organization currently [minus stage checkpoints/multi-thread] otherwise the dump/load would not work. It may even be doing a lot of this for 'directory' mode? The change here is organizing n threads to process them concurrently where possible and coordinating the pipes so they only send data which can be accepted.
> The destination would need to have a multi-thread listen and co-ordinate with the sender on some control channel so feed back completion of each stage.
> Something like a destination host and control channel port to establish the pipes and create additional netcat pipes on incremental ports above the control port for each thread used.
> Dumpall seems like it could be a reasonable start point since it is already doing the complicated bits of serializing the dump data so it can be consistently loaded.
> Probably not really an admin question at this point, more a feature enhancement.
> Is there anything fundamentally wrong that someone with more intimate knowledge of dumpall could point out?
> Thanks
> Tom
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-07-19 02:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-19 22:38 [PATCH 30/33] Alphabetize pg_ls_{tmpdir,archivestatusdir} Justin Pryzby <[email protected]>
2024-07-18 20:53 Re: filesystem full during vacuum - space recovery issues Thomas Simpson <[email protected]>
2024-07-18 22:41 ` Re: filesystem full during vacuum - space recovery issues Ron Johnson <[email protected]>
2024-07-19 02:59 ` Re: filesystem full during vacuum - space recovery issues Scott Ribe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox