Questions tagged [postgresql]
PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.
1,968
questions
0
votes
0
answers
16
views
Random CPU + MEMORY spikes issue on Ubuntu server + Docker + PostgreSQL
I've been encountering an issue that has been bothering me for a while now.
I have a Google Cloud VM running an Ubuntu 18.04 server with several containers, including a PostgreSQL server, a Redis ...
0
votes
1
answer
270
views
How to pg_dump from old version of postgres?
I'm really scratching my head. I was under the impression that newer versions of pg_dump can export from older versions. However, I keep getting this error:
pg_dump: error: aborting because of server ...
0
votes
0
answers
31
views
PostgreSQL + Removing old Host from DB not succeeded
We have HDP cluster with 152 workers machines - worker1.duplex.com .. worker152.duplex.com , While all machines are installed on RHEL 7.9 version
We are trying to delete the last host - worker152....
0
votes
2
answers
138
views
How do I configure SE Linux so PHP can connect to Postgres by socket
I have a PHP application that connects to a Postgresql Database.
This works when I connect via network but I get an SELinux violation when trying to connect using the socket. I believe the contexts ...
0
votes
1
answer
37
views
Data migration between postgresql-9 and postgresql-11 [closed]
What is the best way to migrate postgres-9 database with size of hundreds GB to postgres-11 ? I'm afraid that third party tools will take too long to do this.
0
votes
0
answers
44
views
What are the advantages or disadvantages of using pgx vs postgresql as the database type in crowdsec's configuration?
First I had 'postgres' as the db type in /etc/crowdsec/config.yaml but soon realized that 'postgres' was not a valid value so I changed database type to 'pgx' before I noticed that 'postgresql' was an ...
2
votes
0
answers
68
views
Bacula Postgresql Huge Logs table
We currently have Bacula Enterprise v12 installed on our production.
We seem to have a problem with BWEB regarding the logs of any job. it takes forever for the web interface to load the logs.
After ...
0
votes
0
answers
121
views
Google Cloud SQL [PostgreSQL] Database storage is Increasing
I have a database instance (PostgreSQL v12.6) on Google Cloud and since 10 days ago, the Storage of Database is increasing rapidly.
Here's my Observations:
list of Dtabases
Dashboard of Disk Space
...
0
votes
1
answer
112
views
PostgreSQL Backup: how to activate backup mode non-interactively
Hello PostgreSQL experts!
I'm running a postgresql 15 server with multiple database instances ("clusters"), which uses Netapp NFS as storage for the data and WAL directories. I'm trying to ...
0
votes
2
answers
102
views
Issues with VPS provider limiting CPU usage with data processing Spring Boot application
I developed a Java Spring Boot application with a PostgreSQL database. Since the data being processed is quite a lot, the processing can take time and it uses a lot of CPU (e.g. up to 100% obviously). ...
0
votes
1
answer
554
views
How to install postgres and create new user and db using ansible playbook?
I tried using below playbook to install postgres 12 on my ubuntu machine on both 22 and 23 versions of ubuntu. For installating postgres and set a default password to postgres user and create a new ...
0
votes
1
answer
92
views
Issues connecting Data Fusion to CloudSQL Postgres instance - getting a 403 "The client is not authorized to make this request"
I've got DataFusion and Cloud SQL Postgres up and running in a single GCP project.
I can connect to my postgres instance successfully using pgAdmin (as long as I add my IP address to the allowed ...
0
votes
0
answers
28
views
Postgres related process are running after stopping it
Postgres related process are running after stopping it using the following stop command.
sudo /etc/init.d/postgresql stop
Stopping postgresql (via systemctl): postgresql.service
Postgres process
ps -...
0
votes
0
answers
38
views
AWS RDS Periodic Writes
I have an AWS RDS postgres instance (v15) that doesn't see much usage. However, every three hours at around the half hour mark, I get a flurry of writes (see screenshot, below). At first, I thought ...
0
votes
0
answers
68
views
gcloud sql connect using access token got further but now fails when running psql
GCP recommends forcing SSL for database so I did. Now, when trying to connect, I run
#This cmd is the same with other options too -> gcloud sql generate-login-token
gcloud auth application-...
0
votes
2
answers
890
views
I can't access my Postgres server remotely
I have a Postgres db running on a linux server in my home. I want to be able to access it from my public IP address remotely but it seems not to be working.
I have listen_address = "*" set ...
0
votes
0
answers
217
views
Unable to setup pglogical replication between 2 GCP CloudSQL instances
I'm currently trying to setup pglogical replication between 2 GCP CloudSQL Postgres instances, in order to quickly migrate data between regions.
I've following the GCP docs[1] by creating a replicator ...
1
vote
1
answer
287
views
Having difficulty connecting to PostgreSQL with Django hosted on IIS
Trying to host Django 4 app with PostgreSQL database on Windows Server 2016 with IIS manager 10 and httpPlatformHandler 1.2.
The app and database work without issues when running on a VM server with ...
0
votes
1
answer
72
views
How to use azure functions to do import and export of database oprations on a azure postgres?
How to use azure functions to do import and export of database oprations on a azure postgres?
I have azure postgres flexible server VM and having private vnet for it instead of public endpoint.
Same ...
0
votes
1
answer
131
views
Have you got a worked example of using Postgres through ODBC with openssl and the Progress DataDirect Linux driver?
I am new to openssl configuration, Postgres, and the Progress DataDirect ODBC driver, and I am trying to set this up. I have Postgres working in a container, set up with
tjcw:~$ openssl req -new -x509 ...
2
votes
1
answer
305
views
PostgreSQL huge input lookup error could not parse XML document
I have a PostgreSQL database with some pretty big documents in XML format added into a table which has an XML type field. I am running an XMLTABLE query with Xpath expression and I have the following ...
0
votes
0
answers
19
views
Pg admin not have en US collation, but postgres hawe it
postgre 192.168.0.55
12342 | en_US.utf8 | 11 | en_US.utf8 | en_US.utf8 |
12343 | ru_RU.utf8 | 11 | ru_RU.utf8 | ru_RU.utf8 |
12344 | en_US ...
0
votes
0
answers
191
views
Pgadmin4 - 500 Internal Server Error
I am a new user of PostgreSQL. I have installed PostgreSQL 13 on the CentOS 8 server and configured PGAdmin4 in both the web UI and desktop UI modes. The installation was successful and Desktop UI is ...
0
votes
1
answer
269
views
SSL Certificate loading error in postgresql.conf file during restart
openssl genrsa -out root.key 2048
openssl req -new -key server.key -out server.csr
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
chown postgres:postgres server.*
...
0
votes
0
answers
115
views
Pgpool-II connection pooling with Load balancing disabled still make request to stand by DB node. Causing client to disconnect
I have a problem with pgpool-II load balancer. Currently I have turned off pgpool-II load balancer but somehow it still made request to my standby node. How can this happen? This make my child go ...
0
votes
1
answer
545
views
How do I configure SSL/TLS to connect to a Cloud SQL instance?
As far as I can see connection over SSL/TLS is always available with Cloud SQL. If I enforce it, it becomes required. But the maximum level of protection I was able reach is verify-ca from the ...
0
votes
0
answers
504
views
Using podman/docker secrets to enable SSL for Postgres 15 - SOLVED
I am trying to use podman/docker secrets for ssl certificates but i keep getting SSL errors.
Here is the command i am using:
podman run \
--detach \
--restart on-failure:5 \
--network ...
0
votes
0
answers
688
views
pg_ctl start on Windows gives error: could not open file "global/pg_control": Permission denied
On Windows Server 2022, I am logged in as Administrator and trying to do a pg_ctl start on a Postgresql database that was initially created by user SYSTEM, but get the following error in the cmd ...
0
votes
0
answers
58
views
Issue to configure PostGreSQL as a service on Ubuntu
I'm struggling to deal with PostGreSQL which I'm not able to launch as a service
My conf is set like this:
[Unit]
Description=PostgreSQL RDBMS
Documentation=https://www.postgresql.org/docs/
After=...
0
votes
0
answers
141
views
Allow connections to postgres container from other containers, but no outside machines
Using postgres 14 or 15 as a docker container, with a port in the host bind to the 5432 port of the container, with the following configuration in the pg_hba.conf file
# TYPE DATABASE USER ...
0
votes
0
answers
40
views
Log postgresql query positional parameters on error (postgresql 10)
I am trying to debug an application that inserts data into a PostgreSQL 10 database.
PostgreSQL complains that a value is too long for type varying(10) and logs something like this:
2023-08-22 11:23:...
0
votes
1
answer
1k
views
Is password needed when connecting via Cloud SQL Auth Proxy?
It seems like it's needed, because it asks me for the password. But if so, then what's the point in having 2 credentials (a credentials file + password)?
If not, then what am I missing?
The docs is ...
0
votes
0
answers
292
views
Error running PostgreSQL Deployment in Kubernetes: "No such file or directory" for data directory
I am trying to deploy a PostgreSQL database using Kubernetes and encountered an error related to the data directory. Below is my YAML configuration for the Deployment:
apiVersion: apps/v1
kind: ...
0
votes
1
answer
444
views
How to connect to a GCP Cloud SQL Postgres server using client certs?
I'm attempting to authenticate with a cloud SQL instance using a client cert:
psql -h "$IP" "sslmode=verify-ca sslkey=certs/tls.key sslcert=certs/tls.crt sslrootcert=certs/ca.crt dbname=...
0
votes
0
answers
86
views
Why are changes not synced to replica instances on Kubegres Cluster?
I have set up a Postgres on Kubernetes cluster with Kubegres with 3 instances - 1 master and 2 replicas. Now, I am facing the problem that the replication doesn't really work. What is synced, is when ...
0
votes
0
answers
89
views
pgloader + docker mariadb and postgres = no connection on localhost for my databases
I am trying to migrate from mariaDB to postgres via pgLoader
I have two databases running on my computer
mariadb on 127.0.0.1 pass:pass user:app
postgres on 127.0.0.1 pass:pass user:app
I run the ...
0
votes
0
answers
101
views
How I can use the CPU usage as load balancing weight?
I have the following infrastructure:
Infrastructure is N apps that connect into a master-slave replication schema for Postgresql databases located upon amazon RDS. I use rotated weighted DNS using a ...
0
votes
0
answers
185
views
How can I get the query string, starting by the process id associated to the Postgres connection running it (output of htop)?
I have a machine, let's call it DB_server,
which is connected to two application machines,
let's call them application_server_1 and application_server_2.
DB_server hosts 3 postgres databases on it,
...
4
votes
1
answer
7k
views
Understanding EBSByteBalance% in AWS RDS gp3 volumes
I am troubleshooting an AWS RDS Postgres instance that has been restarted by AWS several times in the last few days, very likely due to resource constraints. It's a testing DB that usually doesn't do ...
0
votes
1
answer
140
views
Why can't I create this role with perms to another role
This doesn't work
CREATE ROLE "role-one" LOGIN;
CREATE ROLE "other_role" LOGIN WITH "role-one";
I get this error
ERROR: unrecognized role option "role-one"
...
0
votes
1
answer
211
views
pgadmin4 using kerberos authentication returns error "Kerberos authentication failed. Couldn't find kerberos ticket"
My environment:
MIT Kerberos server on Red Hat 8.8
PostgreSQL server v15.3 on Red Hat 8.8
PostgreSQL client v15.3 on Ubuntu 22.04 Desktop
I have configured PostgreSQL server to use Kerberos. On my ...
0
votes
0
answers
456
views
How can I configure pg_hba.conf in kubernetes deployment when the ip address of client pods is not known?
Suppose I have two backends be1, be2 deployed in pods that use a postgres server in a pod, and I want to use pg_hba.conf to constrain access for better security, like:
hostssl db1 db1user be1-...
0
votes
0
answers
79
views
Optimzing Docker Builds with docker-compose.yml when using PostGres DB's, performance, etc
I just have a general about how best to build and manage DB servers when using Docker builds with docker-compose.yml. Historically, I've sometimes bound the Postgres DB's to a bound folder on the ...
0
votes
1
answer
191
views
Kubernetes cloudnativepg operator local/nfs backups
I wanted to test the cloudnativepg operator for a local cluster and would like to save the backups on a local dir. The problem is that the documentation only has information regarding cloud solutions ...
0
votes
1
answer
628
views
how to give permissions to postgres_exporter user with ansible?
I need to give some rights to my user postgres_exporter to scrape metrics from postgres to grafana
I can do that on each server by
sudo -iu postgres psql -c 'GRANT pg_read_all_settings TO ...
2
votes
1
answer
459
views
Can't connect Google LookerStudio to Amazon Aurora serverless PostgreSQL
There are no issues connecting to it from any other environment (local works fine, as does from the Cloud), and the security group is currently totally open. An actual PostgreSQL instance, hosted in ...
0
votes
1
answer
1k
views
PostgreSQL client (psql) cannot connect after starting service with pgenv or pg_ctl
I'm able to start the service with pgenv and pg_ctl but cannot connect with psql. The following error shows:
$ psql -l
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432&...
-2
votes
1
answer
885
views
After restarting server Postgres does not start
I am running Postgres 14 on a Windows Server 2022 Standard. While DB operations were running such as inserting, my server restarted, and, now the Postgres does not start. I looked at the config file ...
1
vote
1
answer
4k
views
How to use nginx as ssl reverse proxy for postgresql TCP connection?
What I want to achieve is this
[Postgresql Database] -- transparent TCP -- [clients on the cloud]
[Postgresql Database] -- transparent TCP -- [nginx on jump server] --SSL TCP-- [clients outside the ...
0
votes
1
answer
516
views
How to pass server configuration to postgres docker via a compose file?
I am using Postgres docker 14.4. I want to pass a server configuration (let's say idle_session_timeout) to the postgres docker. The doc gives an example using docker run
docker run -d --name test-db -...