I am getting >ERROR: invalid memory alloc request size 2727388320 after I tried vacuuming in EnterpriseDB. It is getting bigger and bigger. How to solve this issue now ?

I have a postgres cluster (edb) , when i check the status i have this output :

Cluster Status: efm

Agent Type  Address              Agent  DB       VIP
-----------------------------------------------------------------------
Master      xxxxxxxxxxxx.com UP     UP       xxxxxVIP.net*
Standby     xxxxxxxxxxxx.com UP     UP       xxxxxVIP.net

Allowed node host list:
xxxxxxxxxxxxxx.com xx.xx.xx.xx

Membership coordinator: xxxxxxxxxxxxxxxx.com

Standby priority host list:
xxxxxxxxxxxxxxx.com

Promote Status:

DB Type     Address              WAL Received LSN   WAL Replayed LSN   Info
---------------------------------------------------------------------------
Master      xxxxxxxxxxxxxxxxx.com                    71A/69CD6000
Standby     xxxxxxxxxxxxxxxxx.com 71A/69CD6000       71A/69CF5A80

One or more standby databases are not in sync with the master database. The following node(s) returned a WAL LSN that does not match the master: xxxxxxxxxxxxxxxxx.com

Note the standby is not in sync withe master. Can you help me understand why, and what I can do to fix this?

[enterprisedb@pgdvdb1dr bin]$ 2017-06-20 11:34:41: pid 103313: WARNING: could not open configuration file: "pgpool.conf" 2017-06-20 11:34:41: pid 103313: DETAIL: using default configuration parameter values 2017-06-20 11:34:41: pid 103313: FATAL: initializing pool password, failed to open file:"/mnt/hgfs/edb-postgres.auto/pgpool/staging/linux-x64/pgpool/etc/pool_passwd" 2017-06-20 11:34:41: pid 103313: DETAIL: file open failed with error:"No such file or directory" + Exit 3 ./pgpool -n

I need to create a table for caching in the postgresql database. Transactions should be as fast as possible, so i thought of creating unlogged table and have a RAM disk as it's tablespace. There is a warning in the official postgresql documentation : > Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster. I know that unlogged tables are not replicate to the standby and it is okay for me to store unpersistent data in that table. So in case of crash i'm fine with losing it. Here is a questions: 1. Is it safe after all to place a tablespace on the RAM disk in this configuration? (unlogged + RAM disk tablespace + unpersistent data) 2. If it is a safe option should I create ram disk on a replica too, since creation of tablespace will be stored somewhere in wal? I'm using EDB 13.9.13 with streaming replication. If this will workout I will do the same for Postgresql v14.7

I have an EnterpriseDB 11 database server and not clustered. It's a single master database server with no replica. I see an option named synchronous_commit option on the postgres.conf file. It has **synchronous_commit=remote_apply** and on running huge queries it is causing performance degradation. **So should i hash it ?**

What are the advantages of cascading replication in PostgreSQL? I have done testing with three nodes. M1 ----> S1 -----> S2 What is the use if my primary server (M1) goes down (failover)? Where is the secondary standby server (S2) useful for us in this setup?

How to cast **'2015-11-13 00:00:00'** (*text or timestamp*) to **'2015-11-13'**(*date*) in Enterprisedb (EDB) ? With 4 queries below: 1/ SELECT '2015-11-13 00:00:00'::date ; --> result: "2015-11-13" 2/ SELECT date ( '2015-11-13 00:00:00' ) ; 3/ SELECT cast ( '2015-11-13 00:00:00' as date) ; 4/ SELECT to_char('2015-11-13 00:00:00'::timestamp, 'yyyy-mm-dd'::text); In EDB 9.3 , just one query no.4 can cast. And PG 9.3 , all queries can do. Are there still any ways to cast date in EDB ? **EDIT**: With EDB : 1/ SELECT '2015-11-13 00:00:00'::date ; --> result: "2015-11-13 00:00:00" 2/ SELECT date ( '2015-11-13 00:00:00' ) ; --> result: "2015-11-13 00:00:00" 3/ SELECT cast ( '2015-11-13 00:00:00' as date) ; --> result: "2015-11-13 00:00:00" 4/ SELECT to_char('2015-11-13 00:00:00'::timestamp, 'yyyy-mm-dd'::text); --> result: "2015-11-13"

I want to change log_statement value from **all** to **none** on EDB 13 postgresql.conf file. Is the below command is sufficient to reflect above change that are updated in postgresql.conf file?

# su - enterprisedb 
# psql
# postgres> SELECT pg_reload_conf();

Does EnterpriseDB support Oracle RAC to Postgres replication?

To clarify, I am using plain Postgres as packaged by EnterpriseDB (with StackBuilder), *not* EDB Postgres Advanced Server. I'm familiar with using pg_lsclusters, pg_dropcluster, and pg_createcluster on Linux. These tools don't seem to be installed by the Windows x86-64 EnterpriseDB installer (not recognized at the command line), and I don't see any graphical tools that are added to the Start Menu by the installer (which includes the StackBuilder, a Reload Configuration shortcut, and a psql Console).

I have used attached query to check unused indexes in my EDB9.5 DB. It's showing attached indexes are the unused indexes. Is it safe to drop below unused indexes?

SELECT
    idstat.relname AS TABLE_NAME,
    indexrelname AS index_name,
    idstat.idx_scan AS index_scans_count,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    tabstat.idx_scan AS table_reads_index_count,
    tabstat.seq_scan AS table_reads_seq_count,
    tabstat.seq_scan + tabstat.idx_scan AS table_reads_count,
    n_tup_upd + n_tup_ins + n_tup_del AS table_writes_count,
    pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size
FROM
    pg_stat_user_indexes AS idstat
JOIN
    pg_indexes
    ON
    indexrelname = indexname
    AND
    idstat.schemaname = pg_indexes.schemaname
JOIN
    pg_stat_user_tables AS tabstat
    ON
    idstat.relid = tabstat.relid
WHERE
    indexdef !~* 'unique'
	and idstat.relname='cwt_act'
ORDER BY
    idstat.idx_scan DESC,
    pg_relation_size(indexrelid) DESC;

Please suggest!

_I am not from a PostgreSQL background and wanted to use it for a project I am currently working on as it was the recommended DB for what I am using._ _I use PgAdmin 4 to visualise the database, and my literal understanding of "shut down server" on that application was it shut down the database, however it does not. So I wanted to learn how to start/stop/restart the server at the start of the project so I was better equipped to manage it later down the line._ ___
**tl;dr: What is the correct way/command to start a DB, on OSX, not using homebrew, and with it's default settings/directory structure?**
The beginning of my trial and errors... === After much reading on many websites I found the way to stop the server, a way which worked for me personally – not using Homebrew and on OSX **Stopping DB**

$ sudo -u postgres pg_ctl -D /Library/PostgreSQL/12/data/ stop

could not identify current directory: Permission denied
waiting for server to shut down.... done
server stopped

Super! The DB stopped. The website wasn't working, as was excepted. **Attempting to start...** Now, naturally trying to start it back up again with $ sudo -u postgres pg_ctl -D /Library/PostgreSQL/12/data/ start Yeilds...

could not identify current directory: Permission denied
could not identify current directory: Permission denied
could not identify current directory: Permission denied
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "pg_ctl".
Check your installation.

**Uh oh...** So pg_ctl is not in the same directory... perhaps I need to specify it's path in the command

$ sudo -u postgres /Library/PostgreSQL/12/bin/pg_ctl -D /Library/PostgreSQL/12/data/ start
could not identify current directory: Permission denied
could not identify current directory: Permission denied
could not identify current directory: Permission denied
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "pg_ctl".
Check your installation.

**Attempt #1** Trying something else, omitting the -D as I might be overcomplicating things...

$ sudo -u postgres pg_ctl start
could not identify current directory: Permission denied
pg_ctl: no database directory specified and environment variable PGDATA unset

PGDATA isn't set... I have it set on my user, so I guess it isn't set for the postgres user. Let try that then

$ sudo -u postgres export PGDATA="/Library/PostgreSQL/12/data/"
sudo: export: command not found

Okay so it's not that. WELL WHAT THEN?! Reading further I see some using initdb (credit to the [final update in this Q](https://stackoverflow.com/questions/7975556/how-to-start-postgresql-server-on-mac-os-x)) rather than start so I thought I'd give that a go. Nothing else seems to be working so lets try it

$ sudo -u postgres /Library/PostgreSQL/12/bin/pg_ctl -D /Library/PostgreSQL/12/data/ initdb
could not identify current directory: Permission denied
could not identify current directory: Permission denied
could not identify current directory: Permission denied
The program "initdb" is needed by pg_ctl but was not found in the
same directory as "pg_ctl".
Check your installation.

**Attempt #2** Maybe rearrange the command a little bit...

$ sudo su postgres pg_ctl initdb -D /Library/PostgreSQL/12/data/
shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
/Library/PostgreSQL/12/bin/pg_ctl: /Library/PostgreSQL/12/bin/pg_ctl: cannot execute binary file

Reoccurring theme, what is all this "cannot access directories"? I am running sudo and the postgres user as is everyone else in everything I've read! Why does it not work in my case?.. **Attempt #3**

$ ls -la /Library/PostgreSQL/12/data/
ls: : Permission denied

Makes sense... I recall trying to access that /data dir before and it was limited to the postgres user. So let me try doing what I've experimented with so far and attempt ls as postgres

$ sudo -u postgres ls -la /Library/PostgreSQL/12/data/
total 112
[...]

What is this madness?! It works, I have access but I cannot run any of those commands I need to start my DB! sudo has access to all; postgres has access to that dir... **Attempt #4** Lets check some other things.

sudo -u postgres which pg_ctl
/Library/PostgreSQL/12/bin/pg_ctl

So postgres has pg_ctl, and yet cannot run it. Maybe lets try as my user account, forgetting the postgres user

$ pg_ctl initdb --pgdata=/Library/PostgreSQL/12/data/
The files belonging to this database system will be owned by user "Freemium".
**This user must also own the server process.**

The database cluster will be initialized with locale "en_GB.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

initdb: error: could not access directory "/Library/PostgreSQL/12/data": Permission denied
pg_ctl: database system initialization failed

So it cannot be my user account and I don't even want to own that process anyway, it's what the postgres user is for. **Attempt #5** Lets try using root – at this point, why not.

$ sudo pg_ctl initdb --pgdata=/Library/PostgreSQL/12/data/
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

Nope, even root doesn't want it. In both instances it's hinting that it *must* be the postgres user. So why tf is it not working??? **Attempt #6** At this point I just wondered what would happen if I ran the original stop command

$ sudo -u postgres pg_ctl -D /Library/PostgreSQL/12/data/ stop
could not identify current directory: Permission denied

Hmm... Again with the current directory permissions...

After all these attempts, how does one simply start a PostgreSQL server? ---
-----

Local Machine(l1): Windows 10 psql Remote Machine(R1): linux EDB(postgres) On my local I have 1000+ sql script containing insert statement . psql is also installed locally . I need to run this sql script from my local command prompt using psql client . I can connect to remote db as psql -h hostnme -U username -p port DBName . For single file i know that it can be run like \i f.sql . I also tried combining all sql file in one file on windows using command copy /b *.sql new.sql . But the new sql file size is 4 GB . Which is not easy to work with . Is there any way I can run those 1000+ sql script from psql without combining from command prompt ?

I have installed PostgreSQL 9.5 on OSX using the EnterpriseDb package. How do I install PostGIS on it? The official PostGIS OSX Page says that: > The OSX PostgreSQL/Stackbuilder combination from EnterpriseDB has had mixed reliability. Avoid. Are there any options to

Im installing pgAgent using Stack Builder. In one step ask me for service account. Should I use PC, Domain or DB user? Or create a new one? I try using my DOMAIN\my.user.name also using the postgres user and return same error. I dont have the PC_Name\Administrator password > User creation failed. The password specified is incorrect or length of the user name is longer than supported

PostgreSQL has [two distributions for Windows,](https://www.postgresql.org/download/windows/) * EnterpriseDB * BigSQL Do either of these Windows distributions support [automatic updates for the term of support](https://www.postgresql.org/download/windows/) ? Something that looks for the updates online, downloads them, and installs them.

**1/ DESCRIPTION:** - Machine 1 (slave): Centos 6.6 , x64 , installed PostgreSQL 9.3 (on Local) - Machine 2 (master): Centos 6.6 , x64 , installed PostgreSQL 9.3 (on Cloud) Machine 1 (slave) and machine 2 (master) are in a cluster (streaming replication). Sometime, I see "FATAL: terminating walreceiver due to timeout" in slave log. ***Here is full detailed logs:*** **Slave** 2015-03-03 02:01:53 UTC 19693 LOG: database system is ready to accept read only connections 2015-03-03 02:01:53 UTC 19699 LOG: started streaming WAL from primary at 0/8000000 on timeline 1 2015-03-03 02:02:15 UTC 19695 LOG: redo starts at 0/8F04530 2015-03-03 02:39:26 UTC 19699 FATAL: terminating walreceiver due to timeout 2015-03-03 02:39:26 UTC 19695 LOG: invalid record length at 0/8F080F8 2015-03-03 02:39:41 UTC 21065 LOG: started streaming WAL from primary at 0/8000000 on timeline 1 2015-03-03 03:19:12 UTC 21065 FATAL: terminating walreceiver due to timeout 2015-03-03 03:19:12 UTC 19695 LOG: invalid record length at 0/9D488F8 2015-03-03 03:19:27 UTC 22489 LOG: started streaming WAL from primary at 0/9000000 on timeline 1 **Master** 2015-03-03 02:02:40 UTC 1718 LOG: database system is ready to accept connections 2015-03-03 02:02:40 UTC 1724 LOG: autovacuum launcher started 2015-03-03 02:02:42 UTC 1726 [unknown] [unknown]LOG: invalid length of startup packet 2015-03-03 02:02:42 UTC 1726 [unknown] [unknown]LOG: connection failed during start up processing: user= database= 2015-03-03 02:35:45 UTC 1788 pgAdmin III - Query Tool enterprisedbERROR: column "username" does not exist at character 18 2015-03-03 02:35:45 UTC 1788 pgAdmin III - Query Tool enterprisedbSTATEMENT: select datname, username, client_addr, client_port, query from pg_stat_activity; 2015-03-03 02:41:03 UTC 1748 walreceiver enterprisedbLOG: terminating walsender process due to replication timeout 2015-03-03 02:51:42 UTC 3184 ::1 psql.bin enterprisedbERROR: unrecognized configuration parameter "replication_timeout" 2015-03-03 02:51:42 UTC 3184 ::1 psql.bin enterprisedbSTATEMENT: show replication_timeout; 2015-03-03 02:51:54 UTC 3184 ::1 psql.bin enterprisedbERROR: relation "pg_setting" does not exist at character 15 2015-03-03 02:51:54 UTC 3184 ::1 psql.bin enterprisedbSTATEMENT: select * from pg_setting; 2015-03-03 02:58:33 UTC 3388 [unknown] [unknown]LOG: invalid length of startup packet 2015-03-03 02:58:33 UTC 3388 [unknown] [unknown]LOG: connection failed during start up processing: user= database= 2015-03-03 02:58:57 UTC 3390 [unknown] [unknown]LOG: incomplete startup packet 2015-03-03 02:58:57 UTC 3390 [unknown] [unknown]LOG: connection failed during start up processing: user= database= 2015-03-03 03:15:04 UTC 3967 [unknown] enterprisedbFATAL: database "enterprisedb" does not exist 2015-03-03 03:20:53 UTC 2884 walreceiver enterprisedbLOG: terminating walsender process due to replication timeout **2/ QUESTION:** What is about "FATAL: terminating walreceiver due to timeout" problem ? How can I fix it ?

I have a small database PostgreSQL (v9.3) on Centos 6 x64 ( RAM: 8 GB ). **postgresql.conf** max_connections = 512 shared_buffers = 3000MB temp_buffers = 8MB work_mem = 2MB maintenance_work_mem = 128MB effective_cache_size = 3000MB About 150 connections, PostgreSQL takes more than 6 GB of RAM (of course, other applications use about 200 MB of RAM), here my info: Mem: 7062.945M total, 6892.410M used, 170.535M free, 6644.000k buffers Swap: 0.000k total, 0.000k used, 0.000k free, 5378.922M cached ###Questions: 1. Why does PG takes a lot of RAM ? 2. How can I reduce cached buffers of PG ?

Can any one please help me to drop and recreation of sys views in EDB(Postgres).

I am running PostgreSQL 9.6 on windows 10 It seems it has an update monitor that does not use any information about the proxy agent from Windows (we have a corporate proxy). It also does not look at the environment variables HTTP_PROXY or HTTPS_PROXY (which I have set to add credentials via CNTLM for our corporate firewall). This is useful for programs such as NPM and GIT which need to get past our outgoing firewall. So I am getting the following message every 10 or some minutes as a pop up from a program called: "D:\Program Files (x86)\postgresql\updatemonitor\bin\UpdManager.exe" --execute "D:\Program Files\PostgreSQL\9.6\bin\stackbuilder.exe" The message is very deceiving, but I tracked down the program, and it is the one listed above.