Re: Upgrading from Postgresql 9.1 to 10

[Michael Paquier]

On Wed, Feb 14, 2018 at 07:47:55AM -0700, David G. Johnston wrote:
> Zero downtime is only possible by standing up a hot-standby then failing
> over to it.  Same-server upgrade you can do via pg_upgrade but it does
> involve downtime.  There are lots of material and options online, including
> the docs, for setting up hot-standby replication.

PostgreSQL 10 adds logical replication which is able to handle this
scenario with close to zero downtime if you use it with synchronous
replication.  So with 9.1 as origin server you cannot use that.  Logical
decoding facilities being added in 9.4, it could be possible to upgrade
at least from this version using 2nd Quadrant's pglogical (correct me
others here if I am incorrect!).

Now, for older versions, the usual way to do things is by using Slony
which does trigger-based replication.  This can help you reach close to
no downtime, way lower than pg_upgrade for example even if you use its
--link mode.  pg_upgrade --link can work very quickly as well, so if you
care about being close to zero you may want to consider it.
--
Michael


signature.asc
Description: PGP signature

Re: Upgrading from Postgresql 9.1 to 10

[pavan95]

Thankyou Michael



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Remove default privilege from DB

[Durumdara]

Dear Charles!

2018-02-12 10:03 GMT+01:00 Charles Clavadetscher :

> Hi
>
>
>
> *From:* Durumdara [mailto:durumd...@gmail.com]
> *Sent:* Montag, 12. Februar 2018 09:32
> *To:* Postgres General 
> *Subject:* Remove default privilege from DB
>
>
>
> Hello!
>
>
>
> I need to remove default privileges from a Database.
>
> After that some of them remains.
>
>
>
>  Default access privileges
>
>Owner| Schema |   Type   | Access privileges
>
> ++--+---
>
>  postgres   || function | =X/postgres
>
>  postgres   || sequence |
>
>  postgres   || table|
>
>  postgres   || type | =U/postgres
>
>  *suser|| function | =X/suser*
>
> * suser|| sequence |*
>
> * suser|| table|*
>
> * suser|| type | =U/suser*
>
>
>
> How to completely remove the last items?
>
> Could you send me one example?
>
>
>
> I assume
>
>
>
> ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE EXECUTE ON FUNCTIONS FROM
> PUBLIC;
>
> ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE USAGE ON TYPE FROM PUBLIC;
>
>
>
> Bye
>
> Charles
>


After that:

 Default access privileges
   Owner| Schema |   Type   | Access privileges
++--+---
 suser || function |
 suser || sequence |
 suser || table|
 suser || type |
(4 rows)

What are they?

select * from pg_default_acl

24629;0;"r";"{}"
24629;0;"S";"{}"
24629;0;"f";"{}"
24629;0;"T";"{}"

24629 = suser | namespace 0 = none in document
Hmmm... It's very strange for me. I don't find any point which links this
user to this database.

Do you have any idea?

Thanks
  dd

RE: Remove default privilege from DB

[Charles Clavadetscher]

Hi

 

From: Durumdara [mailto:durumd...@gmail.com] 
Sent: Donnerstag, 15. Februar 2018 12:41
To: Charles Clavadetscher 
Cc: Postgres General 
Subject: Re: Remove default privilege from DB

 

Dear Charles!

 

2018-02-12 10:03 GMT+01:00 Charles Clavadetscher mailto:clavadetsc...@swisspug.org> >:

Hi

 

From: Durumdara [mailto:durumd...@gmail.com  ] 
Sent: Montag, 12. Februar 2018 09:32
To: Postgres General mailto:pgsql-gene...@postgresql.org> >
Subject: Remove default privilege from DB

 

Hello!

 

I need to remove default privileges from a Database.

After that some of them remains.

 

 Default access privileges

   Owner| Schema |   Type   | Access privileges

++--+---

 postgres   || function | =X/postgres

 postgres   || sequence |

 postgres   || table|

 postgres   || type | =U/postgres

 suser|| function | =X/suser

 suser|| sequence |

 suser|| table|

 suser|| type | =U/suser

 

How to completely remove the last items? 

Could you send me one example?

 

I assume

 

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE USAGE ON TYPE FROM PUBLIC;

 

Bye

Charles

 

 

After that:

 

 Default access privileges

   Owner| Schema |   Type   | Access privileges

++--+---

 suser || function |

 suser || sequence |

 suser || table|

 suser || type |

(4 rows)


What are they?



I am a bit puzzled. I checked the documentation and execute on function is the 
hard wired default privilege.

https://www.postgresql.org/docs/current/static/sql-grant.html

PostgreSQL grants default privileges on some types of objects to PUBLIC. No 
privileges are granted to PUBLIC by default on tables, table columns, 
sequences, foreign data wrappers, foreign servers, large objects, schemas, or 
tablespaces. For other types of objects, the default privileges granted to 
PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) 
privileges for databases; EXECUTE privilege for functions; and USAGE privilege 
for languages and data types (including domains). The object owner can, of 
course, REVOKE both default and expressly granted privileges.

So after revoking it from public you should actually get an ACL like 
{suser=X/suser} and the entry for the grants should make it disapper.

Example:

 

charles@db.localhost=# \ddp
Default access privileges

Owner | Schema | Type | Access privileges

---++--+---

(0 rows)

 

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

+-+---+---

(0 rows)

 

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc REVOKE 
EXECUTE ON FUNCTIONS FROM public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

 Default access privileges

  Owner   | Schema |   Type   |  Access privileges

--++--+-

charlesc || function | charlesc=X/charlesc

(1 row)

 

Now only user charlesc can execute (new) functions created by himself. This is 
the most typical use case when restricting access to self-made functions.

 

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype |   defaclacl

+-+---+---

   25269137 |   0 | f | {charlesc=X/charlesc}

(1 row)

 

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc GRANT EXECUTE 
ON FUNCTIONS TO public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

 Default access privileges

Owner | Schema | Type | Access privileges

---++--+---

(0 rows)

 

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

+-+---+---

(0 rows)

 

Now again. everybody can execute functions created by charlesc.

What version of PostgreSQL are you using?
And how did you get those first entries at all?

What happens if you issue

ALTER DEFAULT PRIVILEGES FOR ROLE suser GRANT EXECUTE ON FUNCTIONS TO PUBLIC;

again?

Regards
Charles

select * from pg_default_acl

24629;0;"r";"{}"

24629;0;"S";"{}"

24629;0;"f";"{}"

24629;0;"T";"{}"

 

24629 = suser | namespace 0 = none in document

Hmmm... It's very strange for me. I don't find any point which links this user 
to this database.

 

Do you have any idea?

 

Thanks

  dd

 

 

Can parallel vacuum commands lead to a lock in Postgres 10.2

[Meikel Bisping]

Hello,

we have lots of scripts which issue individual vacuum commands to tables 
like "vacuum full gxstage_bs" but also general "vaccum full" commands.

Until now these scripts run sequentially and there are no problems.
Now the idea is to run some scripts in parallel, my question is if 
parallel vacuum commands can lead to a lock in 10.2?
Unfortunately it isn't easy to change the scripts and rely on 
auto-vacuum since they are installed on lots of customers' hosts and 
installing updates is very complex.


Any suggestion is very much appreciated

Meikel Bisping

Trigger (or something similar) on table rename?

[Ken Tanzer]

Hi.  I'm wondering about possibilities for taking action when a table is
renamed.

Specifically in this case, I'm using table_log, which when you use it on a
table creates a new table, sequence and index that is tied to the table
name.  Of course, if the oriignal table is renamed, the other relations
aren't.  So I rename table to table_old, and then create a new version of
table, but the table logging fails because of the already-existing
relations that table_log created.

I could of course rename them manually, or create a function to do it, but
that would still need to be manually invoked.  I haven't really used
listen/notify--I assume it could do this, but there would need to be some
kind of process actively listening?

Something like a trigger on the table rename would be ideal for my
purposes.  Anything like that possible?  Thanks!

Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Trigger (or something similar) on table rename?

[Adrian Klaver]

On 02/15/2018 10:52 AM, Ken Tanzer wrote:
Hi.  I'm wondering about possibilities for taking action when a table is 
renamed.


Specifically in this case, I'm using table_log, which when you use it on 
a table creates a new table, sequence and index that is tied to the 
table name.  Of course, if the oriignal table is renamed, the other 
relations aren't.  So I rename table to table_old, and then create a new 
version of table, but the table logging fails because of the 
already-existing relations that table_log created.


I could of course rename them manually, or create a function to do it, 
but that would still need to be manually invoked.  I haven't really used 
listen/notify--I assume it could do this, but there would need to be 
some kind of process actively listening?


Something like a trigger on the table rename would be ideal for my 
purposes.  Anything like that possible?  Thanks!


Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.


Maybe?:

https://www.postgresql.org/docs/9.6/static/event-triggers.html

https://www.postgresql.org/docs/9.6/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER

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

Re: Trigger (or something similar) on table rename?

[Tom Lane]

Ken Tanzer  writes:
> Something like a trigger on the table rename would be ideal for my
> purposes.  Anything like that possible?  Thanks!

Recent PG versions have "event triggers" which would serve the purpose.
However, the infrastructure for them isn't very fully built out yet.
I'm not sure if you could identify a table rename without resorting to
writing some C code.

regards, tom lane

Re: Trigger (or something similar) on table rename?

[Paul Jungwirth]

On 02/15/2018 10:52 AM, Ken Tanzer wrote:
Hi.  I'm wondering about possibilities for taking action when a table is 
renamed.


I've looked into this a bit. Here is what I understand:

Since 9.3 Postgres has had "event triggers" which can run code on DDL 
events 
(https://www.postgresql.org/docs/current/static/event-triggers.html). 
There are events like `ddl_command_start` and tags like `ALTER TABLE`. 
So you could write a trigger that fires `ON ddl_command_start WHEN TAG 
IN ('ALTER TABLE')`.


Unfortunately I don't think you can get the old/new table name from 
inside the trigger function. If you need that, you might take a look at 
this extension which adds some custom event triggers with ways of 
getting that information:


https://github.com/CartoDB/pg_schema_triggers

If you are really adventurous you could even look at using the 
ProcessUtility hook directly to do what you need.


I'm looking forward to seeing what others say here because I'd like to 
know more myself!


Good luck!

--
Paul  ~{:-)
p...@illuminatedcomputing.com

vacuumdb --all Parallel Feature Request

[Don Seiler]

Good afternoon folks.

I've been playing around with some vacuumdb options this week as part of
post-upgrade testing, in particular with parallel (--jobs=N). I noticed
that vacuumdb --all will only work on one database at a time. This means
that as it winds down to the last few tables in a particular database,
workers will be idle. For example, I ran with 8 jobs, but while 1 job runs
on the last big table of a particular database, the other 7 are doing
nothing.

It would be great if those idle jobs would be able to move on to the next
database to get a head start on the tables there. When you have big tables
in multiple databases in the cluster, it can make a big difference.

Also, running analyze_new_cluster.sh mentions this:

If you would like default statistics as quickly as possible, cancel
> this script and run:
> "/usr/pgsql-9.6/bin/vacuumdb" --all --analyze-only


I wonder if it wouldn't be a bad idea to also mention the --jobs=N
parameter option in that blurb. Yes it's in the --help text but it wouldn't
be bad to highlight its availability.

Don.

-- 
Don Seiler
www.seiler.us

query's performance

[hmidi slim]

Hi,
I have a table establishment which contains these columns: id, name,
longitude, latitude.
I want to select all the establishments in the table establishment within a
radius from a reference point  which is a given establishment.
For example:
I have a given establishment called establishment1 and has an id=1.
I want to display all the establishments within a radius 1km from this
establishment.
I wrote this query:
select e.name,e1.name, e1.longitude, e1.latitude
from establishment as e, establishment as e1
where e.id = 1
and e.id <> e1.id
and ST_DWithin(ST_SetSRID(ST_MakePoint(e1.longitude, e1.latitude),
4326),ST_MakePoint(e.longitude, e.latitude)::geography, 1000) ;

Is it a good practice to iterate the rows of the table like I did and does
not have an effect in the performance if the number of table's rows
increase?

Re: Can parallel vacuum commands lead to a lock in Postgres 10.2

[PT]

On Thu, 15 Feb 2018 17:40:48 +0100
Meikel Bisping  wrote:

> Hello,
> 
> we have lots of scripts which issue individual vacuum commands to tables 
> like "vacuum full gxstage_bs" but also general "vaccum full" commands.
> Until now these scripts run sequentially and there are no problems.
> Now the idea is to run some scripts in parallel, my question is if 
> parallel vacuum commands can lead to a lock in 10.2?
> Unfortunately it isn't easy to change the scripts and rely on 
> auto-vacuum since they are installed on lots of customers' hosts and 
> installing updates is very complex.

They lock already. Running in parallel isn't going to lead to any
new locks.

I don't think that's what you're asking, though. I'm guessing that
what you're asking is are you going to experience deadlocks or other
types of unresolvable lock scenarios.

By design, no. In my experience, no.

In the real world ... it's always possible that you will uncover some
edge-case bug that leads to an unresolvable deadlock. I would rate that
as pretty unlikely, though. Locking is a pretty fundamental part of
Postgres that gets a lot of testing.

-- 
Bill Moran

Re: query's performance

[PT]

On Thu, 15 Feb 2018 22:43:59 +0100
hmidi slim  wrote:

> Hi,
> I have a table establishment which contains these columns: id, name,
> longitude, latitude.
> I want to select all the establishments in the table establishment within a
> radius from a reference point  which is a given establishment.
> For example:
> I have a given establishment called establishment1 and has an id=1.
> I want to display all the establishments within a radius 1km from this
> establishment.
> I wrote this query:
> select e.name,e1.name, e1.longitude, e1.latitude
> from establishment as e, establishment as e1
> where e.id = 1
> and e.id <> e1.id
> and ST_DWithin(ST_SetSRID(ST_MakePoint(e1.longitude, e1.latitude),
> 4326),ST_MakePoint(e.longitude, e.latitude)::geography, 1000) ;
> 
> Is it a good practice to iterate the rows of the table like I did and does
> not have an effect in the performance if the number of table's rows
> increase?

If performance is a concern, don't use long/lat in 2 columns. Instead use a
GEOGRAPHY type to store the point. You can then index on that GEOGRAPHY
column and queries against it will be quite fast.

The way you have it now is going to degrate in performance linearally with
the number of rows in the table. With the proper types and indexes, performance
degredation will be considerably less.

-- 
Bill Moran

postgres not starting

[Azimuddin Mohammed]

Hello,
I am unable to start postgres on one of the server
I am getting below error "HINT: is another postmaster already running on
port 5432, if not wait a few seconds and retry"
I checked the processes nothing is running with postgres

 I think the error caused because I removed everything under /data
directory before stopping the server.
Can someone help.


-- 

Regards,
Azim

Re: postgres not starting

[geoff hoffman]

Restore a backup or reinstall Postgres. The default Postgres data tables need 
to be there and owned by the user Postgres runs as.


> On Feb 15, 2018, at 5:21 PM, Azimuddin Mohammed  wrote:
> 
> Hello, 
> I am unable to start postgres on one of the server 
> I am getting below error "HINT: is another postmaster already running on port 
> 5432, if not wait a few seconds and retry"
> I checked the processes nothing is running with postgres 
> 
>  I think the error caused because I removed everything under /data directory 
> before stopping the server. 
> Can someone help. 
> 
> 
> -- 
> 
> Regards,
> Azim
> 

Re: postgres not starting

[Alan Hodgson]

On Thu, 2018-02-15 at 18:21 -0600, Azimuddin Mohammed wrote:
> Hello, 
> I am unable to start postgres on one of the server 
> I am getting below error "HINT: is another postmaster already running
> on port 5432, if not wait a few seconds and retry"
> I checked the processes nothing is running with postgres 
> 
>  I think the error caused because I removed everything under /data
> directory before stopping the server. 
> Can someone help. 
> 
> 

kill -9 the old processes. The old postmaster is holding onto the port.
And probably some of the old file handles, too, so you might want to
re-init the new database cluster after really cleaning up the old one.

Database health check/auditing

[Tim Cross]

Hi All,

I was wondering if anyone has some pointers to
sites/repositories/resources for scripts to perform basic database
audits and health checks.

situation: I have just commenced a DBA and developer role for an
organisation with a number of Postgres databases (9.4 and 9.6
versions). There has been no dedicated DBA and a number of the databases
were setup by people with little to know Postgres or database
experience. I need to get an overview on what I'm dealing with and start
prioritising what to address first.

It has been some years since I've done any real work with Postgres. Most
of my technical work over the last 10 years has been with Oracle. I
prefer to use scripts over GUI tools like pgAdmin and suspect that there
is probably some good resources out there with existing scripts I can
use as a starting point.

Any pointers greatly appreciated.

thanks,

Tim


-- 
Tim Cross

Re: Database health check/auditing

[Melvin Davidson]

On Thu, Feb 15, 2018 at 9:22 PM, Tim Cross  wrote:

> Hi All,
>
> I was wondering if anyone has some pointers to
> sites/repositories/resources for scripts to perform basic database
> audits and health checks.
>
> situation: I have just commenced a DBA and developer role for an
> organisation with a number of Postgres databases (9.4 and 9.6
> versions). There has been no dedicated DBA and a number of the databases
> were setup by people with little to know Postgres or database
> experience. I need to get an overview on what I'm dealing with and start
> prioritising what to address first.
>
> It has been some years since I've done any real work with Postgres. Most
> of my technical work over the last 10 years has been with Oracle. I
> prefer to use scripts over GUI tools like pgAdmin and suspect that there
> is probably some good resources out there with existing scripts I can
> use as a starting point.
>
> Any pointers greatly appreciated.
>
> thanks,
>
> Tim
>
>
> --
> Tim Cross
>
>




























*Tim, Good luck to you. I have been in your situation a few times.I have
attached the following scripts which I use as a starterto get a general
idea of database status. All of the should run on both 9.4 & 9.4, but
sometimes catalog changes may fudge things up, so you may have to tweak a
bit. I have manymore general queries, so if you have any specific need,
letme know and I'll be glad to send if I have one that fits theneed.The
names should be self descriptive as to what the dobut except for
get_trans_min_cnt.sql (Transaction per minute) none have any DDL.
database_sizes.sqlbad_idx.sqlcache_hit_ratio.sqlget_trans_min_cnt.sqlget_version_num.sqlpg_runtime.sqlpg_stat_all_indexes.sqlpg_stat_all_tables.sqltable_sizes.sqltable_stats.sqluseless_indexes2.sqlPlease
also note I have bash script versions of the same, butas you did not state
the O/S, I felt the sql was best.*--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
SELECT oid, 
   datname, 
   pg_size_pretty(pg_database_size(datname))as size_pretty, 
   pg_database_size(datname) as size,
   (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)  
  FROM pg_database)  AS total,
   ((pg_database_size(datname) / (SELECT SUM( pg_database_size(datname))  
   FROM pg_database) ) * 100)::numeric(6,3) 
AS pct
  FROM pg_database 
  ORDER BY datname;SELECT n.nspname, 
   i.relname, 
   i.indexrelname, 
   CASE WHEN idx.indisprimary 
THEN 'pkey' 
WHEN idx.indisunique 
THEN 'uidx' 
ELSE 'idx' 
END AS type, 
'INVALID' 
  FROM pg_stat_all_indexes i 
  JOIN pg_class c ON (c.oid = i.relid) 
  JOIN pg_namespace n ON (n.oid = c.relnamespace) 
  JOIN pg_index idx   ON (idx.indexrelid =  i.indexrelid ) 
 WHERE idx.indisvalid = FALSE  
 ORDER BY 1, 2;SELECT pg_stat_database.datname, 
   pg_stat_database.blks_read,
   pg_stat_database.blks_hit, 
   round((pg_stat_database.blks_hit::double precision 
  / (pg_stat_database.blks_read 
 + pg_stat_database.blks_hit 
 +1)::double precision * 100::double precision)::numeric, 2) AS 
cachehitratio
   FROM pg_stat_database
  WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
  ORDER BY round((pg_stat_database.blks_hit::double precision 
 / (pg_stat_database.blks_read 
+ pg_stat_database.blks_hit 
+ 1)::double precision * 100::double precision)::numeric, 
2) DESC;
BEGIN;

DROP TABLE IF EXISTS tmp_trans_stats;

CREATE TEMP TABLE tmp_trans_stats AS
SELECT 'start_cnt'::varchar(10) AS taken, 
   SUM(xact_commit + xact_rollback) AS cnt
  FROM pg_stat_database;

COMMIT;

SELECT pg_sleep(60);

INSERT INTO tmp_trans_stats
SELECT 'end_cnt'::varchar(10) AS taken,
   SUM(xact_commit + xact_rollback) AS cnt
  FROM pg_stat_database;
  
SELECT (
(SELECT cnt 
  FROM tmp_trans_stats
 WHERE taken = 'end_cnt')
 - (SELECT cnt 
  FROM tmp_trans_stats
 WHERE taken = 'start_cnt')
   ) as tot_trans;
SELECT current_setting('server_version_num');SELECT pg_postmaster_start_time() as pg_start, 
   current_timestamp - pg_postmaster_start_time() as runtime;SELECT n.nspname as schema,
   i.relname as table,
   i.indexrelname as index,
   i.idx_scan,
   i.idx_tup_read,
   i.idx_tup_fetch,
   CASE WHEN idx.indisprimary
THEN 'pkey'
WHEN idx.indisunique
THEN 'uidx'
ELSE 'idx'
END AS type,
   idx.indisexclusion,
   pg_get_indexdef(idx.indexrelid),
   CASE WHEN idx.indisvalid
THEN 'valid'
ELSE 'INVALID'
END as statusi,
   pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname)) 
as size_in_bytes,

Re: Database health check/auditing

[Tim Cross]

Hi Mel,

thanks a lot. The databases are all running on Red Hat (well OUL to be
specific). The SQL is fine and I can always wrap them in a bash script if
needed.

Having these scripts is a real help. My biggest challenge at the moment is
just turning off my Oracle habits and getting back Postgres ones! Reading
these scripts really helps drag out old forgotten stuff. Luckily, there
doesn't seem to be too much really nasty or weird. For the most part,
doesn't look like anyone has made weird configuration changes and there are
no  obscure triggers doing hidden things. Most of the user defined
functions seem pretty reasonable, though some seem to be doing some pretty
inefficient SQL and unnecessary type casting etc. Privileges and roles are
a mess - seems to be a bit of the 'make  everyone a super user' approach
rather than work out what is required, but I expected that. There are a
couple of databases with considerable size, but many of them are quite
small (I will likely be consolidating a number of servers as their size and
load is low and it will be easier to manage fewer servers). All in all, it
isn't as bad as it could be or as bad as I've seen before, so it shouldn't
bee too bad. Establishing some standards and change control will help.

thanks again,

tim


On 16 February 2018 at 14:11, Melvin Davidson  wrote:

>
>
> On Thu, Feb 15, 2018 at 9:22 PM, Tim Cross  wrote:
>
>> Hi All,
>>
>> I was wondering if anyone has some pointers to
>> sites/repositories/resources for scripts to perform basic database
>> audits and health checks.
>>
>> situation: I have just commenced a DBA and developer role for an
>> organisation with a number of Postgres databases (9.4 and 9.6
>> versions). There has been no dedicated DBA and a number of the databases
>> were setup by people with little to know Postgres or database
>> experience. I need to get an overview on what I'm dealing with and start
>> prioritising what to address first.
>>
>> It has been some years since I've done any real work with Postgres. Most
>> of my technical work over the last 10 years has been with Oracle. I
>> prefer to use scripts over GUI tools like pgAdmin and suspect that there
>> is probably some good resources out there with existing scripts I can
>> use as a starting point.
>>
>> Any pointers greatly appreciated.
>>
>> thanks,
>>
>> Tim
>>
>>
>> --
>> Tim Cross
>>
>>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *Tim, Good luck to you. I have been in your situation a few times.I have
> attached the following scripts which I use as a starterto get a general
> idea of database status. All of the should run on both 9.4 & 9.4, but
> sometimes catalog changes may fudge things up, so you may have to tweak a
> bit. I have manymore general queries, so if you have any specific need,
> letme know and I'll be glad to send if I have one that fits theneed.The
> names should be self descriptive as to what the dobut except for
> get_trans_min_cnt.sql (Transaction per minute) none have any DDL.
> database_sizes.sqlbad_idx.sqlcache_hit_ratio.sqlget_trans_min_cnt.sqlget_version_num.sqlpg_runtime.sqlpg_stat_all_indexes.sqlpg_stat_all_tables.sqltable_sizes.sqltable_stats.sqluseless_indexes2.sqlPlease
> also note I have bash script versions of the same, butas you did not state
> the O/S, I felt the sql was best.*--
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
regards,

Tim

--
Tim Cross

Re: Database health check/auditing

[Melvin Davidson]

On Thu, Feb 15, 2018 at 11:03 PM, Tim Cross  wrote:

> Hi Mel,
>
> thanks a lot. The databases are all running on Red Hat (well OUL to be
> specific). The SQL is fine and I can always wrap them in a bash script if
> needed.
>
> Having these scripts is a real help. My biggest challenge at the moment is
> just turning off my Oracle habits and getting back Postgres ones! Reading
> these scripts really helps drag out old forgotten stuff. Luckily, there
> doesn't seem to be too much really nasty or weird. For the most part,
> doesn't look like anyone has made weird configuration changes and there are
> no  obscure triggers doing hidden things. Most of the user defined
> functions seem pretty reasonable, though some seem to be doing some pretty
> inefficient SQL and unnecessary type casting etc. Privileges and roles are
> a mess - seems to be a bit of the 'make  everyone a super user' approach
> rather than work out what is required, but I expected that. There are a
> couple of databases with considerable size, but many of them are quite
> small (I will likely be consolidating a number of servers as their size and
> load is low and it will be easier to manage fewer servers). All in all, it
> isn't as bad as it could be or as bad as I've seen before, so it shouldn't
> bee too bad. Establishing some standards and change control will help.
>
> thanks again,
>
> tim
>
>
> On 16 February 2018 at 14:11, Melvin Davidson 
> wrote:
>
>>
>>
>> On Thu, Feb 15, 2018 at 9:22 PM, Tim Cross  wrote:
>>
>>> Hi All,
>>>
>>> I was wondering if anyone has some pointers to
>>> sites/repositories/resources for scripts to perform basic database
>>> audits and health checks.
>>>
>>> situation: I have just commenced a DBA and developer role for an
>>> organisation with a number of Postgres databases (9.4 and 9.6
>>> versions). There has been no dedicated DBA and a number of the databases
>>> were setup by people with little to know Postgres or database
>>> experience. I need to get an overview on what I'm dealing with and start
>>> prioritising what to address first.
>>>
>>> It has been some years since I've done any real work with Postgres. Most
>>> of my technical work over the last 10 years has been with Oracle. I
>>> prefer to use scripts over GUI tools like pgAdmin and suspect that there
>>> is probably some good resources out there with existing scripts I can
>>> use as a starting point.
>>>
>>> Any pointers greatly appreciated.
>>>
>>> thanks,
>>>
>>> Tim
>>>
>>>
>>> --
>>> Tim Cross
>>>
>>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> *Tim, Good luck to you. I have been in your situation a few times.I have
>> attached the following scripts which I use as a starterto get a general
>> idea of database status. All of the should run on both 9.4 & 9.4, but
>> sometimes catalog changes may fudge things up, so you may have to tweak a
>> bit. I have manymore general queries, so if you have any specific need,
>> letme know and I'll be glad to send if I have one that fits theneed.The
>> names should be self descriptive as to what the dobut except for
>> get_trans_min_cnt.sql (Transaction per minute) none have any DDL.
>> database_sizes.sqlbad_idx.sqlcache_hit_ratio.sqlget_trans_min_cnt.sqlget_version_num.sqlpg_runtime.sqlpg_stat_all_indexes.sqlpg_stat_all_tables.sqltable_sizes.sqltable_stats.sqluseless_indexes2.sqlPlease
>> also note I have bash script versions of the same, butas you did not state
>> the O/S, I felt the sql was best.*--
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
>
> --
> regards,
>
> Tim
>
> --
> Tim Cross
>
>
Tim,

FYI, the policy in this list is to avoid top posting and bottom post
instead.

Before you do anything else, * check the postgresql.conf* for *shared_buffers,
work_mem & maintenance_work_mem* values.
Quite often the developers have no clue on how to tune a database.

Roles and privileges can always be fixed. At least you don't have to deal
with the case of where they created indexes for every column
in every table, then copied the schema for each clientyeech!

Here's a couple more you may find helpful.

current_queries.sql
triggers.sql

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
SELECT backend_start as be_start,
   datname,
   pid as pid,
   client_addr,
   usename as user,
   state,
   query,
   wait_event_type,  --< COMMENT OUT FOR 9.4 and below
/*   --< UNCOMMENT FOR 9.4 and below
   CASE WHEN waiting = TRUE  
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
*/
   query_start,
   current_timestamp - query_start as duration 
  FROM pg_stat_activity
 WHERE pg_backend_pid() <> pid
ORDER BY 1, 
 datname,
 query_start;

--SELECT * FROM pg_stat_activity LIMIT 2;SELECT n.nspn

Re: postgres not starting

[Laurenz Albe]

Alan Hodgson wrote:
> On Thu, 2018-02-15 at 18:21 -0600, Azimuddin Mohammed wrote:
> > Hello, 
> > I am unable to start postgres on one of the server 
> > I am getting below error "HINT: is another postmaster already running on 
> > port 5432, if not wait a few seconds and retry"
> > I checked the processes nothing is running with postgres 
> > 
> >  I think the error caused because I removed everything under /data 
> > directory before stopping the server. 
> > Can someone help.
> 
> kill -9 the old processes. The old postmaster is holding onto the port.
> And probably some of the old file handles, too, so you might want to re-init 
> the new database
> cluster after really cleaning up the old one.

This seems excessive - maybe the server he wants to start is already
running and there is no need to crash or wipe it.

To give a better answer we would have to know which operating system
it is, which PostgreSQL version and how it was installed.

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

Re: postgres not starting

[Jorge Torralba]

The poster writes 

"  I think the error caused because I removed everything under /data
directory before stopping the server. "

If you did remove the data directory, you will need to kill any processes
hanging on then initdb -D /your/data/directory

Without a data directory it will not start.



On Thu, Feb 15, 2018 at 8:51 PM, Laurenz Albe 
wrote:

> Alan Hodgson wrote:
> > On Thu, 2018-02-15 at 18:21 -0600, Azimuddin Mohammed wrote:
> > > Hello,
> > > I am unable to start postgres on one of the server
> > > I am getting below error "HINT: is another postmaster already running
> on port 5432, if not wait a few seconds and retry"
> > > I checked the processes nothing is running with postgres
> > >
> > >  I think the error caused because I removed everything under /data
> directory before stopping the server.
> > > Can someone help.
> >
> > kill -9 the old processes. The old postmaster is holding onto the port.
> > And probably some of the old file handles, too, so you might want to
> re-init the new database
> > cluster after really cleaning up the old one.
>
> This seems excessive - maybe the server he wants to start is already
> running and there is no need to crash or wipe it.
>
> To give a better answer we would have to know which operating system
> it is, which PostgreSQL version and how it was installed.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>


-- 
Thanks,

Jorge Torralba


Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.

postgres started without auto vaccum

[Azimuddin Mohammed]

Hello All,
I am getting below Warning messages in logs after starting postgres, can
some please help me here

LOG: could not bind socket for statistics collector: Cannot assign
requested address

LOG: disabling statistics collector for lack of working socket

WARNING: autovacuum not started because of misconfiguration

HINT : Enable the "track_counts" option.

MultiXact member wraparound protections are now enabled


I tried enabling track_counts but it did not help.


Any guidance is much appreciated here.


Thanks in Advance!


-- 

Regards,
Azim

Re: postgres started without auto vaccum

[Andreas Kretschmer]

On 16 February 2018 06:54:43 CET, Azimuddin Mohammed  wrote:
>Hello All,
>I am getting below Warning messages in logs after starting postgres,
>can
>some please help me here
>
>LOG: could not bind socket for statistics collector: Cannot assign
>requested address
>
>LOG: disabling statistics collector for lack of working socket
>
>WARNING: autovacuum not started because of misconfiguration
>
>HINT : Enable the "track_counts" option.
>
>MultiXact member wraparound protections are now enabled
>
>
>I tried enabling track_counts but it did not help.
>
>
>Any guidance is much appreciated here.
>
>
>Thanks in Advance!


Which operating system? Please disable all firewalls and try it again.

Regards, Andreas.


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: postgres started without auto vaccum

[Azimuddin Mohammed]

Its on rhel 7.3 , i cant disable firewal, its on one of my company dev
server.

On Feb 16, 2018 12:21 AM, "Andreas Kretschmer" 
wrote:

> On 16 February 2018 06:54:43 CET, Azimuddin Mohammed 
> wrote:
> >Hello All,
> >I am getting below Warning messages in logs after starting postgres,
> >can
> >some please help me here
> >
> >LOG: could not bind socket for statistics collector: Cannot assign
> >requested address
> >
> >LOG: disabling statistics collector for lack of working socket
> >
> >WARNING: autovacuum not started because of misconfiguration
> >
> >HINT : Enable the "track_counts" option.
> >
> >MultiXact member wraparound protections are now enabled
> >
> >
> >I tried enabling track_counts but it did not help.
> >
> >
> >Any guidance is much appreciated here.
> >
> >
> >Thanks in Advance!
>
>
> Which operating system? Please disable all firewalls and try it again.
>
> Regards, Andreas.
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>

Re: postgres started without auto vaccum

[Andreas Kretschmer]

Am 16.02.2018 um 07:23 schrieb Azimuddin Mohammed:
Its on rhel 7.3 , i cant disable firewal, its on one of my company dev 
server.


On Feb 16, 2018 12:21 AM, "Andreas Kretschmer" 
mailto:andr...@a-kretschmer.de>> wrote:


On 16 February 2018 06:54:43 CET, Azimuddin Mohammed
mailto:azim...@gmail.com>> wrote:
>Hello All,
>I am getting below Warning messages in logs after starting postgres,
>can
>some please help me here
>
>LOG: could not bind socket for statistics collector: Cannot assign
>requested address



the stats collector is using UDP sockets, maybe your firewall is 
blocking that.



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: postgres started without auto vaccum

[Pradeep Kumar]

https://bugzilla.redhat.com/show_bug.cgi?id=849428


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Fri, Feb 16, 2018 at 11:51 AM, Andreas Kretschmer <
andr...@a-kretschmer.de> wrote:

> On 16 February 2018 06:54:43 CET, Azimuddin Mohammed 
> wrote:
> >Hello All,
> >I am getting below Warning messages in logs after starting postgres,
> >can
> >some please help me here
> >
> >LOG: could not bind socket for statistics collector: Cannot assign
> >requested address
> >
> >LOG: disabling statistics collector for lack of working socket
> >
> >WARNING: autovacuum not started because of misconfiguration
> >
> >HINT : Enable the "track_counts" option.
> >
> >MultiXact member wraparound protections are now enabled
> >
> >
> >I tried enabling track_counts but it did not help.
> >
> >
> >Any guidance is much appreciated here.
> >
> >
> >Thanks in Advance!
>
>
> Which operating system? Please disable all firewalls and try it again.
>
> Regards, Andreas.
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>
>


-- 
Pradeep

Re: Database health check/auditing

[Thomas Kellerer]

Melvin Davidson schrieb am 16.02.2018 um 05:26:
> Tim,
> 
> FYI, the policy in this list is to avoid top posting and bottom post instead.

Plus: trimming the original content, so that not the whole email thread is 
repeated in the quote.

Thomas