Replication error
I"m running postgres DB on docker & database replication have been enabled. After configuring repmgr not able to switch over/failover operation.DB & errors details are provided below. Please help on this. * ssh -o Batchmode=yes -q -o ConnectTimeout=10 10.0.0.76 /usr/lib/postgresql/9.4/bin/repmgr -f /etc/repmgr.conf --version 2>/dev/null && echo "1" || echo "0"* *ERROR: unable to execute "repmgr" on "10.0.0.76"* *HINT: check "pg_bindir" is set to the correct path in "repmgr.conf"; * *The value is set correctly of binddir. But still getting error. It is adding psql after bin directory and then repmgr i.e /usr/pgsql/pgsql9.5/bin/psql/repmgr. But on primary when I am doing which repmgr it's showing correct path*
Re: Shared memory error
The dynamic_shared_memory_type was set to POSIX . Because of this it was using tmpfs /dev/shm. When the query was running I saw the file system was filling. So I extended the file system and luckily the query worked for that time On Thu, Jun 4, 2020, 5:12 PM Thomas Munro wrote: > >> Do you see any other errors around this one, in the PostgreSQL logs? > > No , only this is the error from db and jdbc end .. and queries are > failing > > If you need a workaround right now you could always set > max_parallel_workers_per_gather=0 so that it doesn't try to use > parallel query. That could make some queries run more slowly. > > When you say you extended the /dev/shm filesystem, what are the mount > options, or how did you configure it? It's interesting that the > problem went away for a while when you did that. >
Re: Shared memory error
Can someone please help on this On Wed, Jun 3, 2020, 11:27 PM Sonam Sharma wrote: > I have set the dynamic_shared_memory_type to none which was set as POSIX. > For now we are not seeing the error... Can someone please explain what is > the impact of setting it as none . > > On Wed, Jun 3, 2020, 8:28 PM Sonam Sharma wrote: > >> Hi Team, >> >> Getting below error when any query is running from jdbc side : >> >> Error : could not open memory segment "/PostgreSQL.1511679208" : No such >> file or directory >> >> Yesterday I have extended the /dev/shm filesystem and the query ran. But >> today again the issue is repeating. >> >> Can someone please help >> >
Re: Shared memory error
Updated jdbc version also On Wed, Jun 3, 2020, 11:46 PM Sonam Sharma wrote: > > > On Wed, Jun 3, 2020, 11:29 PM Adrian Klaver > wrote: > >> On 6/3/20 7:58 AM, Sonam Sharma wrote: >> > Hi Team, >> > >> > Getting below error when any query is running from jdbc side : >> > >> > Error : could not open memory segment "/PostgreSQL.1511679208" : No >> such >> > file or directory >> > >> > Yesterday I have extended the /dev/shm filesystem and the query ran. >> But >> > today again the issue is repeating. >> > >> > Can someone please help >> >> >> To get any useful help you will need to provide: >> >> 1) Postgres version : 11.2 >> >> 2) JDBC driver version : spring-boot-starter-jdbc -1.5.0.Release >> >> 3) OS and version : Linux Red Hat 7.6 >> >> 4) Is this only with one query and if so what is it doing? : No , few >> queries work, few fails .. mostly all are complex select queries. > > > Please let me know if any other info is required.. >
Re: Shared memory error
On Wed, Jun 3, 2020, 11:29 PM Adrian Klaver wrote: > On 6/3/20 7:58 AM, Sonam Sharma wrote: > > Hi Team, > > > > Getting below error when any query is running from jdbc side : > > > > Error : could not open memory segment "/PostgreSQL.1511679208" : No such > > file or directory > > > > Yesterday I have extended the /dev/shm filesystem and the query ran. But > > today again the issue is repeating. > > > > Can someone please help > > > To get any useful help you will need to provide: > > 1) Postgres version : 11.2 > > 2) JDBC driver version : need to check on this > > 3) OS and version : Linux Red Hat 7.6 > > 4) Is this only with one query and if so what is it doing? : No , few > queries work, few fails .. mostly all are complex select queries. Please let me know if any other info is required..
Re: Shared memory error
I have set the dynamic_shared_memory_type to none which was set as POSIX. For now we are not seeing the error... Can someone please explain what is the impact of setting it as none . On Wed, Jun 3, 2020, 8:28 PM Sonam Sharma wrote: > Hi Team, > > Getting below error when any query is running from jdbc side : > > Error : could not open memory segment "/PostgreSQL.1511679208" : No such > file or directory > > Yesterday I have extended the /dev/shm filesystem and the query ran. But > today again the issue is repeating. > > Can someone please help >
Shared memory error
Hi Team, Getting below error when any query is running from jdbc side : Error : could not open memory segment "/PostgreSQL.1511679208" : No such file or directory Yesterday I have extended the /dev/shm filesystem and the query ran. But today again the issue is repeating. Can someone please help
Patroni
Can someone please share steps or any link for how to do set up postgres replication using patroni. And also to test automatic failover. Thanks in advance, Sonam
Re: Replication issue
Yes, you are right. It's for connection failover from application end. I have set up db replication using repmgr. Version : 9.5 On Fri, Apr 17, 2020, 8:15 PM Julien Rouhaud wrote: > On Fri, Apr 17, 2020 at 4:02 PM Adrian Klaver > wrote: > > > > On 4/17/20 6:31 AM, Sonam Sharma wrote: > > > I have setup db replication. And added below parameter in jdbc file . > > > The connection is failing and it's throwing error like db doesn't exist > > > on secondary server. > > > > > > jdbc:postgresql://:50001, > 172.29.195.216:50001/crowd?targetServerType=master=true > > > < > http://172.29.195.216:50001/crowd?targetServerType=master=true > > > > > > > > .PSQLException: FATAL: database ",172.29.195.216 > > > :50001/crowd" does not exist > > > > > > > > > Can someone help on this issue? > > > > > > > Set up replication using what? > > > > The URL above does not look correct to me. Pretty sure the ',' does not > > belong there. > > This was probably intended to be the "connection failover" feature > (https://jdbc.postgresql.org/documentation/head/connect.html). I'm > wondering if the issue is just that the driver is too old to support > that, but I have no idea when this was introduced in jdbc. >
Replication issue
I have setup db replication. And added below parameter in jdbc file . The connection is failing and it's throwing error like db doesn't exist on secondary server. jdbc:postgresql://:50001, 172.29.195.216:50001/crowd?targetServerType=master=true .PSQLException: FATAL: database ",172.29.195.216 <+17229195216>:50001/crowd" does not exist Can someone help on this issue?
Postgres cluster setup
Hello,. Do you have similar setup like Oracle RAC in postgres core . I found in edb but didn't find anything in postgres core. We are looking for setting up replication with no outage and other node will be up , if the primary is down. Any help would be great
Automatic failover
I have setup replication using repmgr. Wanted to know how much time the slave node will take to become new primary ?? If any document, can someone please share of auto failover. With automatic failover, how much time the slave takes to become new primary . Thanks..
V9.5
We have pg_read_all_stats role from v9.6. do we have a similar role for v9.5 and lower versions ?
Trigger
I have a trigger, like many other triggers that fire after update and checks a field of the OLD set. For some reason this trigger throw this error: ERROR: record "old" has no field "ivo_sts_cd" CONTEXT: SQL statement "UPDATE dbo.T8071_CAI_IVO_HDR SET IVO_STS_CD = 1 where T616_VBU_NBR=old.T616_VBU_NBR and T617_FNC_TYP_CD=old.T617_FNC_TYP_CD and T8071_CAI_IVO_ID=old.T8071_CAI_IVO_ID and T8071_ADD_DM= old. T8071_ADD_DM and old.ivo_sts_cd != 10 and old.ivo_sts_cd != 3" PL/pgSQL function dbo.t8096_upd_func02() line 9 at SQL statement SQL state: 42703 this is the trigger : t8096_upd_trig02 AFTER UPDATE ON dbo.t8096_cai_ivo_exc FOR EACH ROW WHEN (old.exc_rsd_idc IS DISTINCT FROM new.exc_rsd_idc) EXECUTE PROCEDURE dbo.t8096_upd_func02() below is the function : CREATE OR REPLACE FUNCTION pps.t8096_upd_func02() RETURNS trigger LANGUAGE plpgsql AS $function$ DECLARE exc_count int; BEGIN select count(*) into exc_count from pps.T8096_CAI_IVO_EXC WHERE T616_VBU_NBR=old.T616_VBU_NBR and T617_FNC_TYP_CD=old.T617_FNC_TYP_CD and T8071_CAI_IVO_ID=old.T8071_CAI_IVO_ID and T8071_ADD_DM= old.T8071_ADD_DM and EXC_RSd_IDC = 'N' AND T8095_EXC_TYP_CD NOT IN(8,9,10,11) ; if exc_count = 0 then UPDATE pps.T8071_CAI_IVO_HDR SET IVO_STS_CD = 1 where T616_VBU_NBR=old.T616_VBU_NBR and T617_FNC_TYP_CD=old.T617_FNC_TYP_CD and T8071_CAI_IVO_ID=old.T8071_CAI_IVO_ID and T8071_ADD_DM= old. T8071_ADD_DM and old.ivo_sts_cd != 10 and old.ivo_sts_cd != 3; end if; RETURN NEW; END $function$; Can someone please help where I am missing .. Thanks, Sonam
Re: Restore is failing
Version is 11.2 .. took schema dump using pg_dump -d -n (schma) / gunzip .. The error I am getting while running restore. On Wed, Feb 5, 2020, 2:03 PM Rob Sargent wrote: > > > On 2/5/20 1:24 AM, Sonam Sharma wrote: > > I am copying schema data to qa box from prod.its failing and giving below > error : > > WARNING: terminating connection because of crash of another server process > > DETAIL: The postmaster has commanded this server process to roll back the > current transaction and exit, because another server process exited > abnormally and possibly corrupted shared memory. > > HINT: In a moment you should be able to reconnect to the database and > repeat your command. > > server closed the connection unexpectedly. > > > Can some one please help. Work_mem is set to 8gb. > > > Thanks , Sonam > > What version of postgres? > Method of copy? > Identify which server process is failing, what's the error there. > >
Restore is failing
I am copying schema data to qa box from prod.its failing and giving below error : WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly. Can some one please help. Work_mem is set to 8gb. Thanks , Sonam
Re: Schema dump
Thanks Adrian, it worked :) On Thu, Jan 2, 2020, 9:50 PM Adrian Klaver wrote: > On 1/2/20 5:47 AM, Tom Lane wrote: > > Sonam Sharma writes: > >> I took a schema dump using : pg_dump -n schema dbname . > >> When I restored this , it doesn't contain the constraints and indexes. > Can > >> someone please help how to take a dump including all > > > > Hmph ... works for me. Where by "works", I mean "the dump contains > > constraints and indexes belonging to tables in the specified schema, > > and not any others". Maybe you could provide a little more detail? > > > > (One thing I notice is that the dump doesn't contain a "CREATE > > SCHEMA schema" command, so you have to do that manually before > > you restore. I guess this fits with the definition of the switch > > as selecting objects *in* the named schema, but it's still a > > possible gotcha.) > > Caffeine kicked in, now I remember how the above happens: > > pg_restore -n utility -f pgrestore_utility.sql /production_12.out > > In pgrestore_utility.sql there is no CREATE SCHEMA. > > This came up in a previous thread: > > https://www.postgresql.org/message-id/6234.1569941612%40sss.pgh.pa.us > > > > > regards, tom lane > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Schema dump
I took a schema dump using : pg_dump -n schema dbname . When I restored this , it doesn't contain the constraints and indexes. Can someone please help how to take a dump including all
Pg import access
How to grant table import access in postgres ?
Re: Schema Copy
The version, I am using is 11.2 On Thu, Dec 5, 2019, 9:29 PM Melvin Davidson wrote: > > Can someone please help in schema copy in same database ? > > A while ago, I created the clone_schema function which will duplicate one > schema to a new schema with the option to copy or not copy data. However, > since there are two versions depending on the version of PostgreSQL you are > using, and you have not specified which version of PostgreSQL you have, I > cannot help you at this time. > > On Thu, Dec 5, 2019 at 9:37 AM Justin wrote: > >> Hi Sonam >> >> If you want a copy the data do a Select * into (newschema.table) from >> oldschema.table >> https://www.postgresql.org/docs/9.1/sql-selectinto.html >> >> If you want to just move the table >> >> https://www.postgresql.org/docs/current/sql-altertable.html >> >> Alter Table oldschema.table Set Schema newschema >> >> On Thu, Dec 5, 2019 at 9:31 AM Sonam Sharma wrote: >> >>> Hi Justin, >>> >>> What can be done to move the data.. >>> >>> On Thu, Dec 5, 2019, 7:57 PM Justin wrote: >>> >>>> Hi Sonam >>>> >>>> As long as the edited sql script has been changed from >>>> oldschema.tables or oldschema.functions etc... to newschema.functions, >>>> newschema.functios etc... >>>> >>>> This does not move data >>>> >>>> On Thu, Dec 5, 2019 at 5:07 AM Sonam Sharma >>>> wrote: >>>> >>>>> Can someone please help in schema copy in same database ? >>>>> >>>>> I have taken db dump of current schema and in the ddl file , I have >>>>> edited it with new schema. >>>>> Will this work? >>>>> >>>> > > -- > *Melvin Davidson* > *Maj. Database & Exploration Specialist* > *Universe Exploration Command – UXC* > Employment by invitation only! >
Re: Schema Copy
Hi Justin, What can be done to move the data.. On Thu, Dec 5, 2019, 7:57 PM Justin wrote: > Hi Sonam > > As long as the edited sql script has been changed from oldschema.tables > or oldschema.functions etc... to newschema.functions, newschema.functios > etc... > > This does not move data > > On Thu, Dec 5, 2019 at 5:07 AM Sonam Sharma wrote: > >> Can someone please help in schema copy in same database ? >> >> I have taken db dump of current schema and in the ddl file , I have >> edited it with new schema. >> Will this work? >> >
Schema Copy
Can someone please help in schema copy in same database ? I have taken db dump of current schema and in the ddl file , I have edited it with new schema. Will this work?
Restore single table
Can someone please help me with the command to restore a single table using psql. Took the backup using below command : pg_dump -t (tablename) dbname > tablename.sql
Index
I have created indexes with certain set of columns. Now I want to remove one of the column and add new column. Can this done without dropping the index?
Create role like role
I have created one role reader and granted usage on schema and select all tables role. I have created one more user and have Grant reader to sonam. But still user Sonam is not able to read the tables.. Anything I am missing please let me know ..
Analyze and vaccum
How auto vaccum and vaccum are different ? How analyze and auto analyze are different ? We were running load on a table which yesterday took 3 mins and today it took 30 mins. The autovaccum and autoanalyze are happening daily . But the manual vaccum and analyze ran on 26th. Can someone please help how these are different and if it help if I run a vaccum and analyze on table .
Trigger
Can someone please help me in how to list all the triggers with their respective tables and the trigger body
Repmgr and pglogical
How repmgr and pglogical are different and which one is more efficient way of replication ? Can someone please help
Query Tuning
We have a query which is running slow and it's taking 26secs to complete.. we have run the analyzer also and it's taking the same time. Any tool is there for query optimization or any suggestions. My query plan looks like this : CTE Scan on approvalwflscreen (cost=8736.21..8737.25 rows=52 width=1214) CTE constants -> Result (cost=0.00..0.01 rows=1 width=44) CTE approval -> Sort (cost=7793.89..7805.22 rows=4530 width=292) Sort Key: apv_1.t616_vbu_nbr, apv_1.t617_fnc_typ_cd, apv_1.t8071_cai_ivo_id, apv_1.t8071_add_dm -> WindowAgg (cost=0.00..7518.80 rows=4530 width=292) -> Nested Loop (cost=0.00..7450.85 rows=4530 width=72) Join Filter: ((apv_1.t8118_apv_sts_cd IS NULL) OR (((apv_1.t8118_apv_sts_cd = con.dummy) OR (apv_1.t8118_apv_sts_cd = con.t8118_rejected) OR (apv_1.t8118_apv_sts_cd = con.t8118_approved) OR (apv_1.t8118_apv_sts_cd = con.t8118_pending)) AND ((apv_1.t8130_apv_job_lvl_cd = con.t8130_deflt) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_processor) OR (apv_1.t81 30_apv_job_lvl_cd = con.t8130_assistant_mgr) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_manager) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_vp) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_re ad_only)) AND (SubPlan 2))) -> CTE Scan on constants con (cost=0.00..0.02 rows=1 width=42) -> Seq Scan on t8119_cai_ivo_apv_wfl apv_1 (cost=0.00..268.18 rows=9818 width=72) SubPlan 2 -> Nested Loop (cost=0.29..3913.17 rows=9507 width=0) -> Seq Scan on t8071_cai_ivo_hdr hdr (cost=0.00..457.98 rows=9760 width=37) Filter: (ivo_sts_cd = ANY (ARRAY[con.dummy, con.t8070_rejct, con.t8070_pndap, con.t8070_aprvd, con.t8070_pndps, con.t8070_cmplt, con.t8070_rdpmt, con.t8 070_stgap, con.t8070_cmeim, con.t8070_pndrv, con.t8070_delet, con.t8070_cncld])) -> Index Only Scan using t8119i0 on t8119_cai_ivo_apv_wfl apv (cost=0.29..0.34 rows=1 width=37) Index Cond: ((t616_vbu_nbr = hdr.t616_vbu_nbr) AND (t617_fnc_typ_cd = hdr.t617_fnc_typ_cd) AND (t8071_cai_ivo_id = hdr.t8071_cai_ivo_id) AND (t8071_add_ dm = hdr.t8071_add_dm)) CTE maxapproval -> Sort (cost=149.09..150.22 <+11490915022> rows=453 width=12) Sort Key: apv_2.joinkey -> HashAggregate (cost=124.58..129.11 <+11245812911> rows=453 width=12) Group Key: apv_2.joinkey, apv_2.t8119_apv_seq_nbr -> CTE Scan on approval apv_2 (cost=0.00..90.60 <+10009060> rows=4530 width=10) CTE header -> Limit (cost=508.37..649.77 <+15083764977> rows=1 width=618) -> Nested Loop (cost=508.37..649.77 <+15083764977> rows=1 width=618) Join Filter: ((hdr_1.ivo_sts_cd = con_1.dummy) OR (hdr_1.ivo_sts_cd = con_1.t8070_rejct) OR (hdr_1.ivo_sts_cd = con_1.t8070_pndap) OR (hdr_1.ivo_sts_cd = con_1.t8070_aprvd) OR (hdr_1.ivo_sts_cd = con_1.t8070_pndps) OR (hdr_1.ivo_sts_cd = con_1.t8070_cmplt) OR (hdr_1.ivo_sts_cd = con_1.t8070_rdpmt) OR (hdr_1.ivo_sts_cd = con_1.t8070_stgap) OR (hdr_1.ivo_sts_cd = con_1.t8070_cmeim) OR (hdr_1.ivo_sts_cd = con_1.t8070_pndrv) OR (hdr_1.ivo_sts_cd = con_1.t8070_delet) OR (hdr_1.ivo_sts_cd = con_1.t8070_cncld)) -> Hash Join (cost=508.37..646.53 <+15083764653> rows=1 width=126) Hash Cond: ((apv_3.t616_vbu_nbr = hdr_1.t616_vbu_nbr) AND (apv_3.t617_fnc_typ_cd = hdr_1.t617_fnc_typ_cd) AND (apv_3.t8071_cai_ivo_id = hdr_1.t8071_cai_ivo_id) AND (a pv_3.t8071_add_dm = hdr_1.t8071_add_dm)) -> CTE Scan on approval apv_3 (cost=0.00..90.60 <+10009060> rows=4530 width=114) -> Hash (cost=306.79..306.79 <+13067930679> rows=10079 width=118) -> Seq Scan on t8071_cai_ivo_hdr hdr_1 (cost=0.00..306.79 rows=10079 width=118) -> CTE Scan on constants con_1 (cost=0.00..0.02 rows=1 width=
Re: Pg_auto_failover
Thanks Paul, I am able to set up. Does pg_auto_failover only works on v10 and v11. I am having 9.5 on production. On Wed, Sep 25, 2019, 11:40 PM Paul Jungwirth wrote: > On 9/25/19 8:55 AM, Sonam Sharma wrote: > > My user has permission to touch inside the Fs. > > I am not getting why it is checking for /backup. > > > > Any suggestions on this , what else I can check > > In addition to the code already linked to, I would look at > > https://github.com/citusdata/pg_auto_failover/blob/1290edd0bab54c627f577cf4462bd16a56b20a1a/src/bin/pg_autoctl/pgctl.c#L429-L437 > > You see that pg_auto_failover is trying to `mkdir -p $pgdata/../backup` > (where pgdata is a C variable, not a shell/environment variable). You > might want to read through the rest of that code to see where pgdata is > coming from, and see if it's being set correctly. Do you really want > /backup at the root level? > > Clearly whatever user is running pg_basebackup doesn't have permission > to make that directory. But maybe that's okay and the directory should > be somewhere else. > > -- > Paul ~{:-) > p...@illuminatedcomputing.com > > >
Re: Pg_auto_failover
My user has permission to touch inside the Fs. I am not getting why it is checking for /backup. Any suggestions on this , what else I can check On Wed, Sep 25, 2019, 12:15 AM bricklen wrote: > > > On Tue, Sep 24, 2019 at 8:41 AM Sonam Sharma wrote: > >> I was setting up pg_auto_failover. Have successfully set up and monitor >> and primary instance. While setting up secondary it's failing with below : >> >> ERROR Failed to ensure empty directory "//backup" : Permission denied >> Error Failed initialise standby server , see above for details. >> > > If you're using the tool from Citus, per > https://github.com/citusdata/pg_auto_failover/blob/d8ba26f47a60eaf1e1dc63ae67279553984d84f5/src/bin/pg_autoctl/file_utils.c#L112 > Is the problem literally that the OS user executing the commands does not > have sufficient permission to work in that directory? If you are creating > your data directory manually in "/", you might want to create a directory > under that path that the user executing the commands has sufficient > permission to modify (delete, create, write to) >
Re: Pg_auto_failover
Hi Rob, I have no where mentioned /backup. I have manually create backup inside / folder to check why it's giving the error. The /backup folder is empty only. On Tue, Sep 24, 2019, 11:15 PM Rob Sargent wrote: > > > > On Sep 24, 2019, at 11:38 AM, Sonam Sharma wrote: > > > > Hi Rob, > > Os : Linux rhel x86_64 > > Postgres version : 11.2 > > Installed the rpm of pg_auto_failover and then set up the monitor, > primary. Secondary is failing with given error > > > > Well //backup is a valid reference to /backup (double slash simply gets > reduced to single slash). Does /backup exist and if so, is it empty. Can > you find “//backup” (or even “backup”) anywhere in the parts you “set up”? > >
Re: Pg_auto_failover
Hi Rob, Os : Linux rhel x86_64 Postgres version : 11.2 Installed the rpm of pg_auto_failover and then set up the monitor, primary. Secondary is failing with given error On Tue, Sep 24, 2019, 10:11 PM Rob Sargent wrote: > > > > On Sep 24, 2019, at 9:41 AM, Sonam Sharma wrote: > > > > I was setting up pg_auto_failover. Have successfully set up and monitor > and primary instance. While setting up secondary it's failing with below : > > > > ERROR Failed to ensure empty directory "//backup" : Permission denied > > Error Failed initialise standby server , see above for details. > > > > Where is it located? > > > > Can someone please help > > “//backup” looks like a typo in the configuration
Fwd: Pg_auto_failover
Can someone please help -- Forwarded message - From: Sonam Sharma Date: Tue, Sep 24, 2019, 9:11 PM Subject: Pg_auto_failover To: pgsql-general I was setting up pg_auto_failover. Have successfully set up and monitor and primary instance. While setting up secondary it's failing with below : ERROR Failed to ensure empty directory "//backup" : Permission denied Error Failed initialise standby server , see above for details. Where is it located? Can someone please help
Pg_auto_failover
I was setting up pg_auto_failover. Have successfully set up and monitor and primary instance. While setting up secondary it's failing with below : ERROR Failed to ensure empty directory "//backup" : Permission denied Error Failed initialise standby server , see above for details. Where is it located? Can someone please help
Re: Pg_auto_failover
I have installed the PostgreSQL 11 from source.(untarred the file and then make install as postgres user since we don't have root access) . Can you please help in installing the extension for pg_auto_failover.. is it possible as postgres user only ?? Thanks, Sonam On Wed, Sep 18, 2019, 8:31 PM Adrian Klaver wrote: > On 9/18/19 7:56 AM, Sonam Sharma wrote: > > Adrian, > > > > Postgres version : 10.7 > > Os version : Linux Redhat 7.4 > > If you are installing from the PGDG repos then the package is there: > > > https://yum.postgresql.org/10/redhat/rhel-7-x86_64/repoview/pg_auto_failover_10.html > > > You can also go here: > > https://github.com/citusdata/pg_auto_failover > > and see your other options. > > > > > On Wed, Sep 18, 2019, 8:01 PM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 9/18/19 7:19 AM, Sonam Sharma wrote: > > > Can someone please guide me in installing pg_auto_failover.. > > where can I > > > get the rpm package for this ? > > > > > > OS and version? > > > > Postgres version? > > > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Fwd: FW: pg_auto_failover
Hi, When I am setting up keeper , [postgres@lablnxdb001 ~]$ pg_autoctl run --pgdata /opt/primary 08:51:03 INFO Managing PostgreSQL installation at "/opt/primary" 08:51:03 INFO PostgreSQL is running in "/opt/primary" on port 6433 08:51:03 ERROR default_version or installed_version for extension "pgautofailover" is NULL 08:51:03 FATAL Failed to check version compatibility with the monitor extension "pgautofailover", see above for details Its failing with above error. The version it’s showing is : [postgres@lablnxdb001 primary]$ pg_autoctl --version pg_autoctl version 1.0.4 I have installed rpm of version 10.7. Can someone please help. *Thanks & Regards,* *Sonam* *Infrastructure System Engineering – DB2 UDB/SYBASE* *[image: cid:image001.png@01D2B91B.44E1A5E0]* -- NOTICE: All information in and attached to the e-mails below may be proprietary, confidential, privileged and otherwise protected from improper or erroneous disclosure. If you are not the sender's intended recipient, you are not authorized to intercept, read, print, retain, copy, forward, or disseminate this message. If you have erroneously received this communication, please notify the sender immediately by phone (704-758-1000) or by e-mail and destroy all copies of this message electronic, paper, or otherwise. By transmitting documents via this email: Users, Customers, Suppliers and Vendors collectively acknowledge and agree the transmittal of information via email is voluntary, is offered as a convenience, and is not a secured method of communication; Not to transmit any payment information E.G. credit card, debit card, checking account, wire transfer information, passwords, or sensitive and personal information E.G. Driver's license, DOB, social security, or any other information the user wishes to remain confidential; To transmit only non-confidential information such as plans, pictures and drawings and to assume all risk and liability for and indemnify Lowe's from any claims, losses or damages that may arise from the transmittal of documents or including non-confidential information in the body of an email transmittal. Thank you.
Pg_auto_failover
Can someone please guide me in installing pg_auto_failover.. where can I get the rpm package for this ?
Re: Uninstall postgres
It's done. I have done it from path . Thanks On Thu, Sep 5, 2019, 2:27 PM Andreas Joseph Krogh wrote: > På torsdag 05. september 2019 kl. 10:53:01, skrev Sonam Sharma < > sonams1...@gmail.com>: > > I have installed postgres with the source code option using configure > --prefix and then make install. Can someone please help in uninstalling > this. How to uninstall the postgres now. The installation was done as > postgres user. > > > make uninstall > > -- > *Andreas Joseph Krogh* > CTO / Partner - Visena AS > Mobile: +47 909 56 963 > andr...@visena.com > www.visena.com > <https://www.visena.com> > >
Re: Uninstall postgres
It's saying gmake *** No rule to make Target uninstall. On Thu, Sep 5, 2019, 2:27 PM Andreas Joseph Krogh wrote: > På torsdag 05. september 2019 kl. 10:53:01, skrev Sonam Sharma < > sonams1...@gmail.com>: > > I have installed postgres with the source code option using configure > --prefix and then make install. Can someone please help in uninstalling > this. How to uninstall the postgres now. The installation was done as > postgres user. > > > make uninstall > > -- > *Andreas Joseph Krogh* > CTO / Partner - Visena AS > Mobile: +47 909 56 963 > andr...@visena.com > www.visena.com > <https://www.visena.com> > >
Uninstall postgres
I have installed postgres with the source code option using configure --prefix and then make install. Can someone please help in uninstalling this. How to uninstall the postgres now. The installation was done as postgres user.
Re: vaccum in background
Thanks, will try both answers. On Wed, Aug 28, 2019, 12:38 PM ROS Didier wrote: > Hi > > It is possible to use background workers with the pg_background extension. > > > > Best Regards > > Didier ROS > > EDF > > > > *De :* sonams1...@gmail.com [mailto:sonams1...@gmail.com] > *Envoyé :* mercredi 28 août 2019 08:59 > *À :* pgsql-general > *Objet :* > > > > Is there any option to run reindex or vaccum in background? > > Every time the session gets logged off in between. > > > Ce message et toutes les pièces jointes (ci-après le 'Message') sont > établis à l'intention exclusive des destinataires et les informations qui y > figurent sont strictement confidentielles. Toute utilisation de ce Message > non conforme à sa destination, toute diffusion ou toute publication totale > ou partielle, est interdite sauf autorisation expresse. > > Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de > le copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou > partie. Si vous avez reçu ce Message par erreur, merci de le supprimer de > votre système, ainsi que toutes ses copies, et de n'en garder aucune trace > sur quelque support que ce soit. Nous vous remercions également d'en > avertir immédiatement l'expéditeur par retour du message. > > Il est impossible de garantir que les communications par messagerie > électronique arrivent en temps utile, sont sécurisées ou dénuées de toute > erreur ou virus. > > > This message and any attachments (the 'Message') are intended solely for > the addressees. The information contained in this Message is confidential. > Any use of information contained in this Message not in accord with its > purpose, any dissemination or disclosure, either whole or partial, is > prohibited except formal approval. > > If you are not the addressee, you may not copy, forward, disclose or use > any part of it. If you have received this message in error, please delete > it and all copies from your system and notify the sender immediately by > return message. > > E-mail communication cannot be guaranteed to be timely secure, error or > virus-free. >
[no subject]
Is there any option to run reindex or vaccum in background? Every time the session gets logged off in between.
Error
I have upgraded my postgres database to 9.2 to 10.9. After upgrade getting below error : Java.lang.illegalstateexception: 3 rows retrieved for single property server is.Checksum. Can some one please help on this issue?
Re: Change in db size
I took the backup using pg_dump with gzip option and restored it with psql. On Wed, Jul 17, 2019, 10:39 PM David G. Johnston wrote: > On Wed, Jul 17, 2019 at 10:03 AM Sonam Sharma > wrote: > >> I have restored database and the db size of source was around 55gb and >> after restore the db size of Target is 47gb. >> > > You haven't described how you restored the database but measuring size > doesn't work because typically restoring a table causes it to be (nearly?) > bloat free while a long running system will accumulate bloat. > > >> How to confirm if restore was successful or not ? >> > > A lack of error messages is a good primary indicator. > > >> The table count is also same. >> > > Good. > > Beyond that you need to decide what amount of internal data validation you > can live with. It also depends on whether you have expected numbers based > upon the data being restored. > > David J. > >
Change in db size
I have restored database and the db size of source was around 55gb and after restore the db size of Target is 47gb. How to confirm if restore was successful or not ? The table count is also same.
Re: table is hanging
I only fetched 2 rows and it's responding On Fri, May 31, 2019, 4:39 PM Nicklas Avén wrote: > To find out if there is some locking problem or just takes time for the > client to handle the data, ask just for a small subset of the table. > > > If > > select * from table limit 1; > > works it is probably just taking a lot of time to send all the data to the > client. > > If the data set includes something large also 1 ow can be heavy. > > You can even try with limit 0 to just find out if the table responses. > > The client software is often the largest bottleneck, not the database. > > > /Nicklas > > > > On 5/31/19 1:03 PM, Sonam Sharma wrote: > > ADding to this, this no of records are present on this table : 19087314 > > On Fri, May 31, 2019 at 4:28 PM Sonam Sharma wrote: > >> Hi Team , >> >> when i am trying to do select on one table , its hanging and not giving >> any output. >> Can someone please advice what should I check for this ? >> >
Re: table is hanging
ADding to this, this no of records are present on this table : 19087314 On Fri, May 31, 2019 at 4:28 PM Sonam Sharma wrote: > Hi Team , > > when i am trying to do select on one table , its hanging and not giving > any output. > Can someone please advice what should I check for this ? >
table is hanging
Hi Team , when i am trying to do select on one table , its hanging and not giving any output. Can someone please advice what should I check for this ?
Re: POSTGRES/MYSQL
Hi Adrian, Ours is retail company and the DB size is Max 30gb, currently we are using db2. On Mon, Mar 11, 2019, 8:21 PM Adrian Klaver wrote: > On 3/11/19 4:31 AM, Sonam Sharma wrote: > > Hi All, > > > > We are planning to migrate our database into any open source DB. > > Can someone please help me in knowing which one will be better among > > POSTGRESQL and MYSQL. > > > > In what terms postgres is better than MYSQL. > This cannot really be answered until more information is provided about > how your current database is used and how you want to continue in the > future. > > > > > Regards, > > Sonam > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
POSTGRES/MYSQL
Hi All, We are planning to migrate our database into any open source DB. Can someone please help me in knowing which one will be better among POSTGRESQL and MYSQL. In what terms postgres is better than MYSQL. Regards, Sonam
Replication
Hi, Can we do master to master replication in Postgres. Regards, Sonam
Re: DB size difference after restore
On Wed, Oct 3, 2018 at 6:21 PM Sonam Sharma wrote: > Hello Ben, > > When we do \l+ , it is different than source, when we load backup from > target server. > Backup is taken using pg_dump and its loaded as psql db name > > Regards, > > Sonam > > > > > > > On Wed, Oct 3, 2018 at 6:17 PM Benjamin Scherrey < > scher...@proteus-tech.com> wrote: > >> If you're talking about space on drive then you can expect the new one to >> be smaller generally as it has been straight efficient writes rather than a >> bunch of updates and deletes which create "holes" in the physical file >> space. >> >> It helps if you are more detailed as to what you've observed if you want >> a more specific answer. >> >> - - Ben Scherrey >> >> On Wed, Oct 3, 2018, 7:43 PM Sonam Sharma wrote: >> >>> I have restored the database from backup dump but the size of source and >>> target databases are different. What can be the reason for this ? >>> >>> Regards, >>> Sonam >>> >>
Re: DB size difference after restore
Hello Ben, When we do \l+ , it is different than source, when we load backup from target server Regards, Sonam On Wed, Oct 3, 2018 at 6:17 PM Benjamin Scherrey wrote: > If you're talking about space on drive then you can expect the new one to > be smaller generally as it has been straight efficient writes rather than a > bunch of updates and deletes which create "holes" in the physical file > space. > > It helps if you are more detailed as to what you've observed if you want a > more specific answer. > > - - Ben Scherrey > > On Wed, Oct 3, 2018, 7:43 PM Sonam Sharma wrote: > >> I have restored the database from backup dump but the size of source and >> target databases are different. What can be the reason for this ? >> >> Regards, >> Sonam >> >
DB size difference after restore
I have restored the database from backup dump but the size of source and target databases are different. What can be the reason for this ? Regards, Sonam
connection error
getting below error from application side : Last acquisition attempt exception: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. the port no is set correct : [postgres@lxsqlptgsdev004 data]$ psql -h localhost -p 5432 psql (9.5.14) Type "help" for help. postgres=# In pg_hba.conf also: # IPv4 local connections: hostall all 127.0.0.1/32trust hostall all 127.0.0.1/23trust hostall all 172.29.161.53/23trust hostall all 172.29.161.52/23trust Can someone please help what can be done to resolve this. Thanks, Sonam
Re: Upgrade/Downgrade
No, I didn't. The version is not compatible with application, so need to downgrade it On Thu, Aug 23, 2018, 4:56 PM Andreas Kretschmer wrote: > On 23 August 2018 12:51:08 CEST, Sonam Sharma > wrote: > >Hello, > > > >My postgres version is 10.4 and I want to downgrade it to 9.5. > >and one is at 9.2 and need to upgrade it to 9.5. > >Can someone please help how to do this. > >Unable to find anything online > > Why downgrade? Dump and restore should do the job, have you tried it? > > Regards, Andreas > > > -- > 2ndQuadrant - The PostgreSQL Support Company > >
Upgrade/Downgrade
Hello, My postgres version is 10.4 and I want to downgrade it to 9.5. and one is at 9.2 and need to upgrade it to 9.5. Can someone please help how to do this. Unable to find anything online
Re: Postgresql
Thank you! Can you please help me with any advantages/disadvantages.. my db size is less than 10gb. I am very new to this. On Sun, Aug 19, 2018, 11:25 PM Adrian Klaver wrote: > On 08/19/2018 10:53 AM, Sonam Sharma wrote: > > > > > > > > > > I am planning to migrate my db from db2 to postgresql. Before that I > > wanted to know is postgresql better than db2? Is it completely free ? > > Any performance issues or any limitations? > > Yes, yes, depends > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Postgresql
I am planning to migrate my db from db2 to postgresql. Before that I wanted to know is postgresql better than db2? Is it completely free ? Any performance issues or any limitations?