Re: pg_multixact/members growing

<thomas.mu...@enterprisedb.com> wrote: > On Wed, May 23, 2018 at 7:49 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Tiffany Thang <tiffanyth...@gmail.com> writes: > >> Our pg_multixact/members directory has been growing to more than 18GB > over > >> the last c

pg_multixact/members growing

Hi, Our pg_multixact/members directory has been growing to more than 18GB over the last couple of months. According to the documentation, the files in there are used to support row locking by multiple transactions and when all tables in all databases are eventually scanned by VACUUM, the older

Database connection log

Hi, Does PostgreSQL keep a log of client connections to the database like Oracle's listener.log? I would like to extract information such as how many connections are made to the database daily, the IP addresses they originated from and the schemas they are connected to. Would it be possible to

Re: Replication questions - read-only and temporary read/write slaves

. Tiff On Wed, Jan 3, 2018 at 11:52 PM, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Fri, Dec 15, 2017 at 12:03:08PM -0500, Tiffany Thang wrote: > > 1. set up a read-only slave database? The closest solution I could find > is > > Hot Standby but the slave would not b

List of objects owned by a schema/owner

Hi, Does anyone have a query that will list all the objects (composite types, sequences, tables, triggers, functions, indices, etc) owned by a schema or owner? I find fragments of information here and there that query on pg_tables, pg_views, etc. Thanks.

Re: List of objects owned by a schema/owner

Thanks Tom for giving me the lead. I’ll take a look. On Fri, Aug 3, 2018 at 10:52 AM Tom Lane wrote: > Tiffany Thang writes: > > Does anyone have a query that will list all the objects (composite types, > > sequences, tables, triggers, functions, indices, etc) owned by a schema

Prefixing schema name

Hi, Would it be possible to create a table or index in a schema without prefixing the schema name? I find it a hassle to keep specifying the schema name when I create an object or performing DMLs in a schema. The search_path configuration works only for queries. For example: Is there a way to run

Re: psql output result

Thanks Geoff and Adrian! On Thu, Mar 15, 2018 at 11:03 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 03/15/2018 07:57 AM, Tiffany Thang wrote: > >> Hi, >> I have a SQL script that does some DDLs, inserts and counts. >> >> The command I ran i

Re: Prefixing schema name

8/03/2018 01:13, David G. Johnston wrote: > > On Wed, Mar 7, 2018 at 4:05 PM, Tiffany Thang <tiffanyth...@gmail.com> > wrote: > >> ​ >> The search_path configuration works only for queries. >> > > ​Um > > ​https://www.postgresql.org/docs/10/stati

psql output result

Hi, I have a SQL script that does some DDLs, inserts and counts. The command I ran is psql dbname -c "\i crscript.sql" > output.txt In output.txt, I got something like INSERT 0 1 INSERT 0 1 CREATE TABLE INSERT 0 2 count --- 9 (1 row) Is there a way to output the SQLs and DDLs so that

Prompt for parameter value in psql

Hi, Would it be possible to prompt for a user input in psql like in Oracle sqlplus? In oracle, we use the & sign, for example, select * from emp where empid= Thanks.

Re: Copy entire schema A to a different schema B

er1 > 4 restore backup to testuser2 schema on other DB? > > Jura. > > On Wed, 20 Feb 2019 at 23:23, Tiffany Thang > wrote: > > > > Hi Adrian, > > I managed to backup my table in parallel using -Fd but I'm back to my > original issue where I could not restore the t

Re: Copy entire schema A to a different schema B

toring. There is > even an option to copy the data or not. Default is not. > > On Thu, Feb 21, 2019 at 3:23 PM Adrian Klaver > wrote: > >> On 2/21/19 11:52 AM, Tiffany Thang wrote: >> > Thanks everyone. Unfortunately the schema rename would not work since >> &g

Re: Copy entire schema A to a different schema B

Hi Ron, How would that work if I'm only interested in importing/refreshing a single table in a target schema that contains several other tables? Thanks. Tiff On Wed, Feb 20, 2019 at 5:36 PM Rob Sargent wrote: > > On 2/20/19 3:22 PM, Tiffany Thang wrote: > > Hi Adrian, > I manag

Re: Copy entire schema A to a different schema B

. Tiff On Mon, Feb 11, 2019 at 11:53 AM Adrian Klaver wrote: > On 2/11/19 8:30 AM, Tiffany Thang wrote: > > Thanks Adrian and Ron. Sorry, I was not clear. What I'm trying to > > achieve was to dump the schema quickly and be able to restore a single > > or subset of objects from

Re: Copy entire schema A to a different schema B

Thanks Adrian! Tiff On Mon, Feb 11, 2019 at 11:53 AM Adrian Klaver wrote: > On 2/11/19 8:30 AM, Tiffany Thang wrote: > > Thanks Adrian and Ron. Sorry, I was not clear. What I'm trying to > > achieve was to dump the schema quickly and be able to restore a single > > or s

Copy entire schema A to a different schema B

Hi, To copy the source schema A to target schema B in the same database in PG10.3, I use psql to dump schema A and manually removes anything specific to the schema in the text dump file before importing into schema B. How do I achieve the same exporting from Schema A and importing into schema B

Re: Copy entire schema A to a different schema B

? Are there any other alternatives? Thanks. Tiff On Mon, Feb 11, 2019 at 11:10 AM Ron wrote: > On 2/11/19 10:00 AM, Tiffany Thang wrote: > > Hi, > > To copy the source schema A to target schema B in the same database in > > PG10.3, I use psql to dump schema A and manually remove

multiple indexes on the same column

Hi, Can someone explain the use of creating multiple indexes on the same column? How would the optimizer determine which index to use? From my brief testing, the optimizer picked the latest created index, testidx3. Can you provide a scenario where creating multiple indexes on the same column

Re: multiple indexes on the same column

Got it! Thanks Andres and Tom! Tiff On Fri, Apr 12, 2019 at 1:07 PM Tom Lane wrote: > Andres Freund writes: > > On 2019-04-12 09:51:51 -0400, Tom Lane wrote: > >> Tiffany Thang writes: > >>> Can someone explain the use of creating multiple in

Restrict user to create only one db with a specific name

Hi, Is there a way in PostgreSQL 12 to restrict user to creating a database with a specific database name? For example, userA can only create a database with a name called mydb. Any other names would generate an error. If that is not possible, will it be possible then to limit userA to creating

Re: Restrict user to create only one db with a specific name

ase "userB" owner "userB"; > CREATE DATABASE > > When, say, "userB" goes away, his or her data will go the way all things > do: > > drop database "userB"; > drop role "userB"; > > Or did I misunderstand you? > > Che

Re: Capturing just slow queries

ng solutions: > https://wiki.postgresql.org/wiki/Monitoring > > > Tiffany Thang schreef op do 16-07-2020 om 13:41 [-0400]: > > Hi, > > log_min_duration_statement captures all statements including DMLs > > that have exceeded the threshold. Is there a way in PG 12 to ca

Capturing just slow queries

Hi, log_min_duration_statement captures all statements including DMLs that have exceeded the threshold. Is there a way in PG 12 to capture just select statements excluding all DMLs and DDLs? In my environment, it's acceptable for DMLs and DDLs to cross the threshold and we are more interested in

Dropping dependent tables

Hi, I would like to write a SQL statement to drop all the tables owned by me but a problem I’m struggling with is with referential integrity. The statement I have now to generate the drop statements is select 'drop table '||tablename||' cascade;' from pg_tables where tableowner=''; The

Re: Dropping dependent tables

On Thu, Jun 3, 2021 at 10:18 AM Tom Lane wrote: > Tiffany Thang writes: > > I would like to write a SQL statement to drop all the tables owned by me > > but a problem I’m struggling with is with referential integrity. The > > statement I have now to generate the drop state

Error creating postgres extensions

Hi, I'm having trouble finding in the documentation the steps to install the postgres extensions such as pg_stat_statements and pg_trgm on PG13.1/CentOS7. Can someone please assist? postgres=# create extension pg_stat_statements; ERROR: could not open extension control file

Re: Error creating postgres extensions

On Tue, May 4, 2021 at 10:04 PM Ian Lawrence Barwick wrote: > 2021年5月5日(水) 10:43 Tiffany Thang : > > > > Hi, > > > > I'm having trouble finding in the documentation the steps to install the > postgres extensions such as pg_stat_statements and pg_trgm on > P

Managing major PostgreSQL upgrades

Hi, Every year we spent a lot of time planning and manually performing major PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering if there is a better way of managing these upgrades through automation. Can anyone share their experiences? Thanks. Tiff

Re: Managing major PostgreSQL upgrades

what problems to expect during a major upgrade which makes automating the process difficult. > > On Mon., Nov. 15, 2021, 6:45 a.m. Tiffany Thang, > wrote: > >> Hi, >> Every year we spent a lot of time planning and manually performing major >> PostgreSQL upgrade on many of

Re: Profiling a function call

Thanks, Jan and Pavel. I will check out the tracer. Tiff On Sun, May 21, 2023 at 7:43 AM Pavel Stehule wrote: > > > ne 21. 5. 2023 v 13:30 odesílatel Jan Wieck napsal: > >> On 5/20/23 00:36, Tiffany Thang wrote: >> > Hi, >> > I have a function tha

Profiling a function call

Hi, I have a function that has been executing for a long time and not returning any results. Wait event=NULL so it seems like it is still executing and not waiting on any specific resources. Is there a way to profile the function call to get an idea of what it is currently executing within the