Re: how to check that recovery is complete

[Laurenz Albe]

On Thu, 2020-11-05 at 19:03 +, Dmitry O Litvintsev wrote:
> I have a workflow where I recover from PITR backup and run a query on it. The 
> program that runs query 
> checks that it can connect to database in a loop, until it can, and then runs 
> the query. 
> This has worked fine far. Recently I upgraded to 11 and I see that I can 
> connect to DB while recovery is 
> not complete yet.

That's because "hot_standby = on" by default from v10 on.

You could change the configuration and set it to "off",
then PostgreSQL will continue to work as before.

The alternative way is running this after you connect:

  SELECT pg_is_in_recovery();

If that returns TRUE, recovery is not done yet.
Back out, wait a while, then try again.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Query a column with the same name as an operator

[Java Developer]

Hello,

I am trying to query a column from a table I migrated from my MYSQL
into POSTGRESQL but I seem to be having issues with a few column names.

the column name cast is also an operator, I think zone may also be a
problem.

MYSQL: OK
SELECT id, start_date, local_time, country, city, region, temperature,
cast, humidity, wind, weather, zone FROM w_records WHERE city =
'Edinburgh' AND start_date LIKE '%2020-11-01%' ORDER BY id DESC;

I can run the SELECT * from FROM w_records WHERE city = 'Edinburgh' but
the above does not work.

Any idea how I can run a query that accept table name that is already a
Operator?

Many Thanks 

Re: Query a column with the same name as an operator

[Magnus Hagander]

On Fri, Nov 6, 2020 at 10:00 AM Java Developer  wrote:
>
> Hello,
>
> I am trying to query a column from a table I migrated from my MYSQL
> into POSTGRESQL but I seem to be having issues with a few column names.
>
> the column name cast is also an operator, I think zone may also be a
> problem.
>
> MYSQL: OK
> SELECT id, start_date, local_time, country, city, region, temperature,
> cast, humidity, wind, weather, zone FROM w_records WHERE city =
> 'Edinburgh' AND start_date LIKE '%2020-11-01%' ORDER BY id DESC;
>
> I can run the SELECT * from FROM w_records WHERE city = 'Edinburgh' but
> the above does not work.
>
> Any idea how I can run a query that accept table name that is already a
> Operator?

Yes, cast is a keyword in SQL (not an operator). To use it as a column
name you have to quote it, like
SELECT "cast" FROM test
(and the same when you create the table, or indeed any references to the column)

zone is not, and should be fine.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Re: Query a column with the same name as an operator

[Jurrie Overgoor]

On 06-11-2020 10:08, Magnus Hagander wrote:

On Fri, Nov 6, 2020 at 10:00 AM Java Developer  wrote:

Hello,

I am trying to query a column from a table I migrated from my MYSQL
into POSTGRESQL but I seem to be having issues with a few column names.

the column name cast is also an operator, I think zone may also be a
problem.

MYSQL: OK
SELECT id, start_date, local_time, country, city, region, temperature,
cast, humidity, wind, weather, zone FROM w_records WHERE city =
'Edinburgh' AND start_date LIKE '%2020-11-01%' ORDER BY id DESC;

I can run the SELECT * from FROM w_records WHERE city = 'Edinburgh' but
the above does not work.

Any idea how I can run a query that accept table name that is already a
Operator?

Yes, cast is a keyword in SQL (not an operator). To use it as a column
name you have to quote it, like
SELECT "cast" FROM test
(and the same when you create the table, or indeed any references to the column)

zone is not, and should be fine.



There is a list of keywords available in the documentation here: 
https://www.postgresql.org/docs/current/sql-keywords-appendix.html


Maybe it's of help to you.


Jurrie

Re: pgagent

[Gabi Draghici]

Hi,

1. The job it's a simple call to one stored function like that :
BEGIN
 CALL other_user_name.get_function();
END;
Problem is that despite the fact that it's long passed by the scheduled
running time and I tried the "Run now" option (from pgadmin) a couple of
times, there is no evidence that the job actually runned ! Nothing in
pgagent log, main log or in pgagent.pga_joblog or pgagent.pga_jobsteplog.
Is there any way to trace of debug this ?

2. Yes, pg_cron also looks good and it's my second option if I can't make 1
to work.

Regards,
Gabi


On Thu, Nov 5, 2020 at 11:34 PM Adrian Klaver 
wrote:

> On 11/5/20 1:07 PM, Gabi Draghici wrote:
> >
> > Hi,
> >
> > I have installed postgresql 12 on sles 15 for some tests. Now I'm
> > interested in some sort of scheduler and from what I've read so far,
> > pgagent should do the job. So I've installed pgagent 4.0. I've added a
> > job (which I can see in pgagent.pga_job) but everytime I ran it (from
> > pgadmin) nothing happens ! When I start the pgagent I can see "...
> > pgagent ... connection authorized" in the main log. What else should I
> > check ?
>
> pg_cron:
>
> https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/
>
> For pgagent what is the job doing and when? Could it be it hasn't run
> because it has not reached it's scheduled time.
>
> >
> > Thanks,
> > Gabi
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: pgagent

[Adrian Klaver]

On 11/6/20 4:26 AM, Gabi Draghici wrote:

Hi,

1. The job it's a simple call to one stored function like that :
BEGIN
  CALL other_user_name.get_function();
END;
Problem is that despite the fact that it's long passed by the scheduled 
running time and I tried the "Run now" option (from pgadmin) a couple of 
times, there is no evidence that the job actually runned ! Nothing in 
pgagent log, main log or in pgagent.pga_joblog or pgagent.pga_jobsteplog.

Is there any way to trace of debug this ?



Is the pgagent daemon running?

You could crank up the the log_statement to 'all'(temporarily as this 
can generate a lot of logs) in postgresql.conf and reload the server. 
Then tail the Postgres log file when click on 'Run now' to see what 
happens.




2. Yes, pg_cron also looks good and it's my second option if I can't 
make 1 to work.


Regards,
Gabi


On Thu, Nov 5, 2020 at 11:34 PM Adrian Klaver > wrote:


On 11/5/20 1:07 PM, Gabi Draghici wrote:
 >
 > Hi,
 >
 > I have installed postgresql 12 on sles 15 for some tests. Now I'm
 > interested in some sort of scheduler and from what I've read so far,
 > pgagent should do the job. So I've installed pgagent 4.0. I've
added a
 > job (which I can see in pgagent.pga_job) but everytime I ran it
(from
 > pgadmin) nothing happens ! When I start the pgagent I can see "...
 > pgagent ... connection authorized" in the main log. What else
should I
 > check ?

pg_cron:

https://www.citusdata.com/blog/2020/10/31/evolving-pg-cron-together/


For pgagent what is the job doing and when? Could it be it hasn't run
because it has not reached it's scheduled time.

 >
 > Thanks,
 > Gabi
 >
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pgagent

[Gabi Draghici]

It't not a daemon yet (I started manually) but yes, it's running :

postgres@dbdocs:~> ps aux | grep postgresql
postgres  2093  0.0  0.3 8720088 218280 ?  Ss   17:54   0:00
/usr/lib/postgresql12/bin/postgres -D /opt/postgresql/database
postgres  2315  0.0  0.0  64664  5708 pts/2S17:57   0:00
/usr/bin/postgresql12-pgagent hostaddr=10.1.0.4 dbname=postgres
user=pgagent -s /opt/postgresql/pglog/pg_agent.log
postgres  2326  0.0  0.0   8696   820 pts/2S+   17:57   0:00 grep
--color=auto postgresql

I've switched log_statement to 'all' and restarted the DB. All I see it's a
bunch of statements like these :

2020-11-06 18:07:03.869 EET postgres pgagent [2316]LOG:  statement: SELECT
J.jobid   FROM pgagent.pga_job J  WHERE jobenabledAND jobagentid IS
NULLAND jobnextrun <= now()AND (jobhostagent = '' OR jobhostagent =
'dbdocs-prd') ORDER BY jobnextrun
2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG:  statement:
/*pga4dash*/
SELECT 'session_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT count(*) FROM pg_stat_activity) AS "Total",
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'active')  AS
"Active",
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle')  AS "Idle"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS
"Transactions",
  (SELECT sum(xact_commit) FROM pg_stat_database) AS "Commits",
  (SELECT sum(xact_rollback) FROM pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT 'ti_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT sum(tup_inserted) FROM pg_stat_database) AS "Inserts",
  (SELECT sum(tup_updated) FROM pg_stat_database) AS "Updates",
  (SELECT sum(tup_deleted) FROM pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT 'to_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT sum(tup_fetched) FROM pg_stat_database) AS "Fetched",
  (SELECT sum(tup_returned) FROM pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT 'bio_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT sum(blks_read) FROM pg_stat_database) AS "Reads",
  (SELECT sum(blks_hit) FROM pg_stat_database) AS "Hits"
) t

2020-11-06 18:07:05.459 EET postgres postgres [2104]LOG:  statement:
/*pga4dash*/
SELECT 'session_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT count(*) FROM pg_stat_activity) AS "Total",
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'active')  AS
"Active",
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle')  AS "Idle"
) t
UNION ALL
SELECT 'tps_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database) AS
"Transactions",
  (SELECT sum(xact_commit) FROM pg_stat_database) AS "Commits",
  (SELECT sum(xact_rollback) FROM pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT 'ti_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT sum(tup_inserted) FROM pg_stat_database) AS "Inserts",
  (SELECT sum(tup_updated) FROM pg_stat_database) AS "Updates",
  (SELECT sum(tup_deleted) FROM pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT 'to_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT sum(tup_fetched) FROM pg_stat_database) AS "Fetched",
  (SELECT sum(tup_returned) FROM pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT 'bio_stats' AS chart_name, row_to_json(t) AS chart_data
FROM (SELECT
  (SELECT sum(blks_read) FROM pg_stat_database) AS "Reads",
  (SELECT sum(blks_hit) FROM pg_stat_database) AS "Hits"
) t
..

Regards,
Gabi








On Fri, Nov 6, 2020 at 5:38 PM Adrian Klaver 
wrote:

> On 11/6/20 4:26 AM, Gabi Draghici wrote:
> > Hi,
> >
> > 1. The job it's a simple call to one stored function like that :
> > BEGIN
> >   CALL other_user_name.get_function();
> > END;
> > Problem is that despite the fact that it's long passed by the scheduled
> > running time and I tried the "Run now" option (from pgadmin) a couple of
> > times, there is no evidence that the job actually runned ! Nothing in
> > pgagent log, main log or in pgagent.pga_joblog or pgagent.pga_jobsteplog.
> > Is there any way to trace of debug this ?
>
>
> Is the pgagent daemon running?
>
> You could crank up the the log_statement to 'all'(temporarily as this
> can generate a lot of logs) in postgresql.conf and reload the server.
> Then tail the Postgres log file when click on 'Run now' to see what
> happens.
>
> >
> > 2. Yes, pg_cron also looks good and it's my second option if I can't
> > make 1 to work.
> >
> > Regards,
> > Gabi
> >
> >
> > On Thu, Nov 5, 2020 at 11:34 PM Adrian Klaver  > > wrote:
> >
> > On 11/5/20 1:07 PM, Gabi Draghici wrote:
> >  >
> >  > Hi,
> >  >
> >  > I have installed postgresql 12 on sles 15 for some tests. Now I'm
> >  > interested in some sort of scheduler and fro

Re: pgagent

[Adrian Klaver]

On 11/6/20 8:12 AM, Gabi Draghici wrote:


It't not a daemon yet (I started manually) but yes, it's running :

postgres@dbdocs:~> ps aux | grep postgresql
postgres  2093  0.0  0.3 8720088 218280 ?      Ss   17:54   0:00 
/usr/lib/postgresql12/bin/postgres -D /opt/postgresql/database
postgres  2315  0.0  0.0  64664  5708 pts/2    S    17:57   0:00 
/usr/bin/postgresql12-pgagent hostaddr=10.1.0.4 dbname=postgres 
user=pgagent -s /opt/postgresql/pglog/pg_agent.log
postgres  2326  0.0  0.0   8696   820 pts/2    S+   17:57   0:00 grep 
--color=auto postgresql




I've switched log_statement to 'all' and restarted the DB. All I see 
it's a bunch of statements like these :


2020-11-06 18:07:03.869 EET postgres pgagent [2316]LOG:  statement: 
SELECT J.jobid   FROM pgagent.pga_job J  WHERE jobenabled    AND 
jobagentid IS NULL    AND jobnextrun <= now()    AND (jobhostagent = '' 
OR jobhostagent = 'dbdocs-prd') ORDER BY jobnextrun
2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG:  statement: 
/*pga4dash*/


The above is from when you click 'Run now'?



Regards,
Gabi











--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Need help with PITR for PostgreSQL 9.4.5

[Sri Linux]

Hi All

I have to start Postgres 9.4.5 vacuum for our production environment. Got
interrupted with the Linux session, is there a way I can monitor if the
vacuum is progressing while I reconnect to the Linux box?

Thanks
Sri

On Thu, Jun 25, 2020 at 1:16 PM Sri Linux  wrote:

> Thanks for your support.
>
> I will try to restore and provide results shortly without restoring
> pg_xlog file
>
> Regards,
> Sri
>
> On Thu, Jun 25, 2020 at 1:09 PM Stephen Frost  wrote:
>
>> Greetings,
>>
>> * Sri Linux (srilinu...@gmail.com) wrote:
>> > Please find the method used. Please recommend me if I have done
>> > something wrong...
>>
>> Yes, you are using 'cp' which is *not* recommended for an archive
>> command.
>>
>> > Performing a hot backup using pg_basebackup:
>> > Create a new folder as the postgres user
>> > pg_basebackup --xlog --format=t -D /server01/pgbackup/`date %Y%m%d`
>>
>> Note that your pg_basebackup is going to be copying WAL also, in
>> addition to the archive_command you've configured.
>>
>> > Restoring from Backup:
>> > Extract the contents of base.tar from the backed up folder on top of
>> the PostgreSQL installation folder:
>> > tar -xf base.tar -C /var/lib/pgsql/9.4/data (RedHat/CentOS)
>> > Assuming that there is a single database tar file (named with a number)
>> in the backup, extract the contents of this folder to the /server01 folder:
>> > tar -xf .tar -C /server01
>> > Copy any unarchived WAL log files saved from the first step back into
>> the pg_xlog folder appropriate for the OS
>>
>> Not sure what "first step" means here, but you are configuring PostgreSQL
>> with a recovery.conf later with a restore command to fetch the WAL it
>> needs
>> from your archive, so you shouldn't be needing to copy files from one
>> pg_xlog to another (which is just generally a bad idea..).
>>
>> Further, the error you're getting, as mentioned, is actually that you've
>> somehow ended up with WAL for some other cluster in your archive and
>> when this instance tries to restore it, it complains (quite
>> understandably).  A sensible tool would prevent this from being able to
>> happen by checking that the WAL that's being archived to a given
>> location matches the database that the WAL is for.
>>
>> As mentioned, you should really be considering using a purpose-built
>> tool which manages this for you, such as pgbackrest, which has such
>> checks and provides you with backup/restore commands.
>>
>> Thanks,
>>
>> Stephen
>>
>

Re: Need help with PITR for PostgreSQL 9.4.5

[David G. Johnston]

On Friday, November 6, 2020, Sri Linux  wrote:

> Hi All
>
> I have to start Postgres 9.4.5 vacuum for our production environment. Got
> interrupted with the Linux session, is there a way I can monitor if the
> vacuum is progressing while I reconnect to the Linux box?
>
>>
>>>
Please start new email threads when you have new questions.

If you run vacuum manually in a session, and then disconnect that session,
the vacuum stops just like any other command.

David J.

Re: Need help with PITR for PostgreSQL 9.4.5

[Sri Linux]

Thank you very much David



On Fri, Nov 6, 2020 at 2:11 PM David G. Johnston 
wrote:

> On Friday, November 6, 2020, Sri Linux  wrote:
>
>> Hi All
>>
>> I have to start Postgres 9.4.5 vacuum for our production environment. Got
>> interrupted with the Linux session, is there a way I can monitor if the
>> vacuum is progressing while I reconnect to the Linux box?
>>
>>>

> Please start new email threads when you have new questions.
>
> If you run vacuum manually in a session, and then disconnect that session,
> the vacuum stops just like any other command.
>
> David J.
>
>

Re: Need help with PITR for PostgreSQL 9.4.5

[Sri Linux]

David

Could you please provide me the link to start new questions?

Thanks and Regards
Sri

On Fri, Nov 6, 2020 at 3:27 PM Sri Linux  wrote:

> Thank you very much David
>
>
>
> On Fri, Nov 6, 2020 at 2:11 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Friday, November 6, 2020, Sri Linux  wrote:
>>
>>> Hi All
>>>
>>> I have to start Postgres 9.4.5 vacuum for our production environment.
>>> Got interrupted with the Linux session, is there a way I can monitor if the
>>> vacuum is progressing while I reconnect to the Linux box?
>>>

>
>> Please start new email threads when you have new questions.
>>
>> If you run vacuum manually in a session, and then disconnect that
>> session, the vacuum stops just like any other command.
>>
>> David J.
>>
>>

Re: Need help with PITR for PostgreSQL 9.4.5

[David G. Johnston]

On Fri, Nov 6, 2020 at 2:34 PM Sri Linux  wrote:

> Could you please provide me the link to start new questions?
>
>>
>>>
Assuming you are using Gmail...

https://business.tutsplus.com/tutorials/how-to-compose-and-send-your-first-email-with-gmail--cms-27678

David J.

Single user model vaccum

[Sri Linux]

Hi All

How can I monitor and make sure that the vacuum is still running if I get
lost with the session to Linux putty? I would need to check if the
vacuum is still running from Linux OS.

Please advise.

Regards
Sri

Re: Single user model vaccum

[Sri Linux]

Added''

when I log in to the Linux terminal and grep process, I can only see the
single-user mode process running but not sure about vacuum status. I can't
run a pg_stat commands from pgsql as the db is in single-user mode.

 ps -ef|grep -i sql
root 25412 15199  0 14:59 pts/100:00:00 su postgres -c
/usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres
postgres 25413 25412 53 14:59 ?02:50:00 /usr/pgsql-9.4/bin/postgres
--single -O -D /pgsql/9.4/data postgres

Thanks,
Sri

On Fri, Nov 6, 2020 at 8:17 PM Sri Linux  wrote:

> Hi All
>
> How can I monitor and make sure that the vacuum is still running if I get
> lost with the session to Linux putty? I would need to check if the
> vacuum is still running from Linux OS.
>
> Please advise.
>
> Regards
> Sri
>

Re: Single user model vaccum

[David G. Johnston]

On Friday, November 6, 2020, Sri Linux  wrote:

> Added''
>
> when I log in to the Linux terminal and grep process, I can only see the
> single-user mode process running but not sure about vacuum status. I can't
> run a pg_stat commands from pgsql as the db is in single-user mode.
>
>  ps -ef|grep -i sql
> root 25412 15199  0 14:59 pts/100:00:00 su postgres -c
> /usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres
> postgres 25413 25412 53 14:59 ?02:50:00
> /usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres
>
> On Fri, Nov 6, 2020 at 8:17 PM Sri Linux  wrote:
>
>> Hi All
>>
>> How can I monitor and make sure that the vacuum is still running if I get
>> lost with the session to Linux putty? I would need to check if the
>> vacuum is still running from Linux OS.
>>
>
Don’t really know but a quick read suggests you will need to basically
debug the running process.

In the future use something like “screen” or “tmux” so you can just
re-attach to the terminal container.

I’m not sure if there is anything special about single-user mode (way to
omit critical info on your first two emails) that would discourage you from
just killing the server and starting a new vacuum.

David J.

Re: Single user model vaccum

[Sri Linux]

Thank you David

vacuum is done but the application is very slow, is this normal behavior?
Will it run any background process after vacuum causing this slowness?

Thanks
Sri

On Fri, Nov 6, 2020 at 9:15 PM David G. Johnston 
wrote:

>
> On Friday, November 6, 2020, Sri Linux  wrote:
>
>> Added''
>>
>> when I log in to the Linux terminal and grep process, I can only see the
>> single-user mode process running but not sure about vacuum status. I can't
>> run a pg_stat commands from pgsql as the db is in single-user mode.
>>
>>  ps -ef|grep -i sql
>> root 25412 15199  0 14:59 pts/100:00:00 su postgres -c
>> /usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres
>> postgres 25413 25412 53 14:59 ?02:50:00
>> /usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres
>>
>> On Fri, Nov 6, 2020 at 8:17 PM Sri Linux  wrote:
>>
>>> Hi All
>>>
>>> How can I monitor and make sure that the vacuum is still running if I
>>> get lost with the session to Linux putty? I would need to check if the
>>> vacuum is still running from Linux OS.
>>>
>>
> Don’t really know but a quick read suggests you will need to basically
> debug the running process.
>
> In the future use something like “screen” or “tmux” so you can just
> re-attach to the terminal container.
>
> I’m not sure if there is anything special about single-user mode (way to
> omit critical info on your first two emails) that would discourage you from
> just killing the server and starting a new vacuum.
>
> David J.
>
>

Re: Single user model vaccum

[David G. Johnston]

On Friday, November 6, 2020, Sri Linux  wrote:

> Thank you David
>
> vacuum is done but the application is very slow, is this normal behavior?
> Will it run any background process after vacuum causing this slowness?
>

Please don’t top-post.

Are you still running under single-user?

David J.

Re: Single user model vaccum

[Sri Linux]

Thank you very much David,
No, vacuum activity wiht single-mode user is completed. I restarted all
services and the application is online but pretty slow.I will open a new
case but confused about how to do a new post in the Postgres community.

is this common for an application to run pretty slow after vacuum without
any other option like full etc.

Regards
Sri

On Fri, Nov 6, 2020 at 9:55 PM David G. Johnston 
wrote:

> On Friday, November 6, 2020, Sri Linux  wrote:
>
>> Thank you David
>>
>> vacuum is done but the application is very slow, is this normal behavior?
>> Will it run any background process after vacuum causing this slowness?
>>
>
> Please don’t top-post.
>
> Are you still running under single-user?
>
> David J.
>

Re: Single user model vaccum

[David G. Johnston]

On Friday, November 6, 2020, Sri Linux  wrote:

> Thank you very much David,
> No, vacuum activity wiht single-mode user is completed. I restarted all
> services and the application is online but pretty slow.I will open a new
> case but confused about how to do a new post in the Postgres community.
>
> is this common for an application to run pretty slow after vacuum without
> any other option like full etc.
>

There isn’t a process for “opening a new case”.  You just send an email.

David J.

Re: Single user model vaccum

[Sri Linux]

Ok, thank you.

On Fri, Nov 6, 2020 at 10:13 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Friday, November 6, 2020, Sri Linux  wrote:
>
>> Thank you very much David,
>> No, vacuum activity wiht single-mode user is completed. I restarted all
>> services and the application is online but pretty slow.I will open a new
>> case but confused about how to do a new post in the Postgres community.
>>
>> is this common for an application to run pretty slow after vacuum without
>> any other option like full etc.
>>
>
> There isn’t a process for “opening a new case”.  You just send an email.
>
> David J.
>

After vacuum application runs very slow ? is this common behavior ?

[Sri Linux]

Hi All,

Our production database size is about 2TB and we had run into issues and
Postgres log did recommend running the vacuum in single-user mode. We have
successfully completed running a single user mode vacuum without any
options. .. services were online after vacuum. Application load time and
response times are very poor and don't load sometimes. Is this common
behavior after a vacuum? DO we need to consider any post activities or give
some time for the database to settle down?

Did full os restart for the DB server?

Thanks
Sri.

Re: After vacuum application runs very slow ? is this common behavior ?

[Sri Linux]

Adding version info

Postgres 9.4.5
Linux - rhel 7.3

Thanks
Sri

On Fri, Nov 6, 2020 at 10:20 PM Sri Linux  wrote:

> Hi All,
>
> Our production database size is about 2TB and we had run into issues and
> Postgres log did recommend running the vacuum in single-user mode. We have
> successfully completed running a single user mode vacuum without any
> options. .. services were online after vacuum. Application load time and
> response times are very poor and don't load sometimes. Is this common
> behavior after a vacuum? DO we need to consider any post activities or give
> some time for the database to settle down?
>
> Did full os restart for the DB server?
>
> Thanks
> Sri.
>

Re: After vacuum application runs very slow ? is this common behavior ?

[Adrian Klaver]

On 11/6/20 8:20 PM, Sri Linux wrote:

Hi All,

Our production database size is about 2TB and we had run into issues and 
Postgres log did recommend running the vacuum in single-user mode. We 
have successfully completed running a single user mode vacuum without 
any options. .. services were online after vacuum. Application load time 
and response times are very poor and don't load sometimes. Is this 
common behavior after a vacuum? DO we need to consider any post 
activities or give some time for the database to settle down?


Given that there is no actual useful/hard data in the above the chances 
of there being a relevant answer in return is doubtful.


Also given your subsequent post the major version you are using is 
nearing 1 year past EOL and is 21 minor releases behind the last release 
in the series. At the very least you need to bring it up to the last 
minor release.




Did full os restart for the DB server?

Thanks
Sri.



--
Adrian Klaver
adrian.kla...@aklaver.com