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,935
questions
0
votes
0
answers
10
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 -...
- 293
1
vote
1
answer
43
views
Emergency database does not shut down
I did
pgsqld stop
and the command line was like
Stopping PostgreSql:
and I have waited for seconds, and I pressed ctrl+C to cancel it, because I just thought something is going wrong.
and I tried it ...
- 17
0
votes
1
answer
26
views
how to operate replicated database properly
Im setting up a replication database with postgres.
and it seems there are much informations about how to setting up, but I still can't find how to operate it in application.
What I want is to 'select'...
- 17
0
votes
1
answer
27
views
Why it asks me to input password here to restart db
I just have done
pg_basebackup -h primary_host -p primary_port -D PGDATA -U replication user
and I jsut wanted to restart the db, so I did
sudo su - postgres
sudo service postgres restart
and the ...
- 17
2
votes
1
answer
54
views
How do I give a postgres user all privileges to a specific database?
I'm setting up a new database server which I intend to use as a backend for many services. Each of these services generally only care about a single database in the system, so I'd like to isolate the ...
- 121
0
votes
1
answer
50
views
Postgres 14 won't start
Windows 10 Pro | Postgres 14.
last log:
2023-02-13 10:15:04.673 GMT [7956] LOG: received fast shutdown request
2023-02-13 10:15:04.675 GMT [7956] LOG: aborting any active transactions
2023-02-13 10:...
- 21
0
votes
1
answer
34
views
Command `postgres --version` shows incorrect version installed
I recently wanted to upgrade my Postgres version from 14.2 to 15.
The first thing I did, was delete all postgres related directories:
michael@local var % postgres --version
postgres (PostgreSQL) 14.2
...
- 101
0
votes
1
answer
379
views
What's wrong in my docker file - services.db.environment must be a mapping
Here's my yml contents.
version: '2.2'
services:
# postgres
# https://hub.docker.com/_/postgres
db:
container_name: postgres
platform : linux/amd64
image: "postgres:14.6-...
0
votes
0
answers
13
views
Sudden Increase in row exclusive locks and connection exhaustion in PostgreSQL
I have a scenario that repeats itself every few hours. In every few hours, there is a sudden increase in row exclusive locks in PostgreSQL DB. In Meantime there seems that some queries are not ...
- 117
0
votes
0
answers
121
views
Simultaneous Use in freeRadius 3.0
I install Ubuntu server 22.04
I install apache2
I install PHP
I install PostgreSQL and phpPgAdmin
I install freeradius 3.0
ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/...
- 1
1
vote
1
answer
25
views
Allowing Linux accounts to log into PostgreSQL db
I have Ubuntu server with several users. I want to allow any of those users to access a PostgreSQL database installed also in that server using their respective Linux usernames and passwords without ...
0
votes
1
answer
165
views
HAProxy and postgres
My haproxy configuration is as below, in the backend there is pg_autoctl cluster, another VM (bunty4) hosts the monitor and haproxy is installed on it.
global
maxconn 100
defaults
log global
...
- 3
0
votes
0
answers
37
views
How to create a copy of a user in AWS RDS PostgreSQL?
I need to replace a AWS RDS PostgreSQL user without causing an outage during the switch, so I'm trying to duplicate it's permissions and can't seem to find information on how to do it correctly.
In ...
- 1
0
votes
1
answer
21
views
Can you configure PostgreSQL to distinguish between client and server logs?
Scenario: You are hosting PostgreSQL for others and want to offer a good service.
As someone who hosts the database, I want to see when Postgres crashes and other more admin-related information.
...
0
votes
1
answer
120
views
Bash loop through dates
I'm trying to copy data from psql via a bash script now I'm stuck with a loop my bash script is as following:
#!/bin/bash
DATEBEGIN=2016-03-01
DATEEND=2016-03-31
DATEMONTH=2016-03
echo "Copy ...
- 27
0
votes
1
answer
98
views
Upgrading Fedora Server to 37, Postgres' upgrade didn't work: ERROR: could not access file "$libdir/postgis-3"
After everything else was shown to be working fine (YAY!), Postgres refused to start (don't be thrown by the name - Postgres was packaged as PostgreSQL in praise of Paul Aoki's conversion of the QUEL ...
- 1,202
0
votes
0
answers
122
views
Unable to connect to the database
I am using docker-compose to set up umami.But it seems that umami is not up. I have checked log file umami. Here it is what looks like.
yarn run v1.22.19
$ npm-run-all check-db update-tracker start-...
0
votes
1
answer
59
views
Do AWS replicas of Postgres RDS instances require Vacuum and Analyze independently of their primary?
Assuming that I am not using logical replication...
I'm not clear on how the low level replication interacts with other low level operations like vacuum and analyze.
I'm not clear on whether replicas ...
- 1,621
0
votes
1
answer
94
views
Which command to restart postgresql 12 service
I am about to restart postgresql 12 service running on Ubuntu 18.
First, I check it's status : sudo service postgresql status. Its says :
postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/...
- 1
0
votes
0
answers
152
views
Install postgres-client-13 on debian buster
I need to run pg_dump from a Node.js docker image (node:16-buster) to a postgres 13.6 docker image.
The problem is that for Buster version the only package available for postgresql-client is 11 and I ...
0
votes
1
answer
631
views
Connecting to hosts postgresql from django app running in a docker container
I am trying to connect to my hosts postgresql server (version 14) from my django app running in a docker container.
here are my settings
docker-compose.yml:
version: '3'
services:
web:
build: ...
- 123
0
votes
0
answers
19
views
Setting up second PGSQL when first is using built-in PostgreSQL module
I have a Centos 8 server running PostgreSQL 10. I would like to add a PostgreSQL 14 database to this server (which would run on a different port), which will replace the PostgreSQL 10 instance after a ...
- 1
3
votes
1
answer
198
views
How do I ensure a docker network (interface) exists even when it's not running?
I have a container stack (specified by a docker-compose.yml). The stack requires a PostgreSQL database, but I am using a locally running native instance instead of having it be a part of the stack (to ...
- 132
0
votes
1
answer
304
views
Patroni Haproxy and pg-bouncer architecture
I'm building a patroni postgres cluster, however, I can't decide which is better architecture
App > HAproxy > Pgbouncer > PostgreSQL (patroni)
App > PGBouncer > HAproxy > PostgreSQL (...
0
votes
0
answers
148
views
How can I restore Postgres DB with pg_basebackup in my case?
I have two DB servers and backup which is configured by pg_basebackup. You can see whole command below.
pg_basebackup -h $PGHOST -p $PGPORT -U $PGUSER -w -D $backup_dir/psql_base_backup_$(date +"%...
- 11
0
votes
1
answer
28
views
Unable to create PostgreSQL user over TLS using createuser
I need to create a new PostgreSQL user on a remote PostgreSQL 13 server from on an Ubuntu 18.04 machine. createuser and psql (postgresql-client) are on the Ubuntu server. I was hoping I could use ...
- 29
0
votes
0
answers
25
views
PostgreSQL Subscribers and Publishers (Multi-Master Scenario)
I'm currently trying to develop a solution that would be trying to focus on Multi-Master Databases
So here is how it's supposed to operate
I want so that whenever an Agent makes a change to a database,...
- 101
0
votes
0
answers
42
views
Can't run psql backup in user-data script in AWS EC2
I have private subnet for my EC2 instance, I use NAT Gateway for it to access internet. I created user-data script for my EC2 where I've created docker-compose.yml with postgres service defined. After ...
- 41
0
votes
0
answers
188
views
Access a Docker container remotely using my domain
I have a computer running Debian 11 and I’m trying to set up Supabase self-hosting. The Supabase official documentation only goes as far as starting the Docker containers and accessing them from http:/...
- 101
0
votes
0
answers
76
views
Is PostgreSQL 13 slower than 11?
I've set up 2 VirtualBox machines with the same parameters.
The first one with Debian 10 - PostgreSQL 11, the second with Debian 11 - PostgreSQL 13.
The installation is only the base system + SSH ...
0
votes
1
answer
42
views
Postgresql 6.9.24 on Oracle Linux 8.6
I'm trying to install Postgresql 9.6.24 on Oracle Linux 8.6 as it contains some bug fixes and other patches that we need.
This concrete version is not present in the Oracle Linux 8 repositories, only ...
- 105
0
votes
0
answers
154
views
Postgres processes exceed max_worker_processes?
I have three Docker containers: one running Postgres and two others running LoopbackJS (the actual setup is more complex, but I'm simplifying here for clarity).
The Loopback servers read/write to a ...
- 1
0
votes
0
answers
23
views
LOAD BLOB COLUMN INSTEAD OF FILE
Is there a way to load data from blob column instead of file like
LOAD DATA FROM (SELECT A.B FROM A) INTO TABLE TEMP FIELDS
TERMINATED BY ' ' ENCLOSED BY '"' LINES TERMINATED BY '\n'
instead of ...
- 11
0
votes
0
answers
417
views
Postgres running in WSL all of a sudden drops network and gives "Connection refused"
We have two laptops in the team experiencing the same kind if issues, but they behave a bit differently so I'll take it from the point of view of mine...
Both laptops runs updated Windows 11 with ...
- 109
0
votes
0
answers
42
views
How to scale a postgress database correctly?
We have software which has a lot of customers.
At the moment the customers have a database on premise (each with a own installation within their company) and we plan to move into the cloud.
We will be ...
0
votes
1
answer
437
views
Error running an Docker container or docker compose with postgres, golang and Debian 11, Agora appbuilder backend
I spun up a Debian 11 EC2 on AWS, and installed postgres 14.5 on it and docker and docker compose on it.I added a superuser to postgres of "admin' with a password. I created my docker-compose.yml ...
- 37
1
vote
1
answer
56
views
Why do emails to a user in my database with an alias domain hit the server but get bounced (and the inbox is not created)?
;tldr
Can anyone tell me what's wrong? Why do emails to a user in my database with an alias domain hit the server but get bounced (and the inbox is not created)?
Background
I have postfix pgsql set up ...
- 11
0
votes
1
answer
2k
views
Installation of postgresql-15 seems to fail
Apologies in advance if this is a stupid question - I am a developer not a sysadmin.
Since running apt-get update/upgrade on my ubuntu VM (Oracle VirtualBox) I cannot seem to get my postgresql ...
- 49
1
vote
1
answer
53
views
tmpfs /run 99% used, but it is not, so postgresql unable to start
Today I rebooted my home server and got error in postgres log:
2022-09-15 10:35:19.926 +07 [5312] FATAL: could not write lock file
"postmaster.pid": No free space on device pg_ctl:
could ...
- 171
0
votes
0
answers
277
views
gvmd service not starting after postgresql replication - openvas
I deployed two openvas servers on two distinct VMs (Debian 10) with postgresql version 11. I replicated the postgres 11 using with hot standby configuration, the data is getting replicated but after ...
- 1
0
votes
0
answers
215
views
Alternatives to RDS Proxy for PG serverless database usage
What other managed Postgres database services exist that are designed for serverless use?
i.e hosted databases that have a similar proxy layer that can handle thousands of connection requests spawned ...
- 143
1
vote
0
answers
160
views
Postgresql - Master\Slave Async Replication - How to quickly fail back and forth?
I have two hosts set up as very simple master\slave. All servers connect to the floating IP 10.1.1.10.
Host A:
Designation: Master
Floating IP - 10.1.1.10
Standard IP - 10.1.1.11
relevant pg_hba....
2
votes
0
answers
266
views
access to a postgres database gets permission denied if startet over systemctl
I have an issue with a postgres database running on my CentOS Server, but only if i start it over systemctl.
My Service config:
Description=Nakama server
[Service]
ExecStart=/etc/nakama/nakama --...
- 21
0
votes
1
answer
458
views
Error starting PostgreSQL: could not open directory / not a directory
Today, a sudden error started occurring on a PostgreSQL 12 in Ubuntu 20. Before this, the system that uses the db (Odoo) had been working without an issue:
2022-08-25 00:50:04.580 UTC [34164] LOG: ...
- 11
1
vote
1
answer
545
views
RDS PostgreSQL Import+Export to/from S3
Is it possible to enable both importing and exporting with an RDS PostgreSQL instance to an S3 bucket? I've been able to use the following pattern to enable one or the other with consistent success:
...
- 384
1
vote
0
answers
1k
views
Docker compose permission denied when PHP-FPM trying to create directory
I have this docker-compose I'm experimenting with. Im using fastCGI from Nginx to run the script.
The docker-compose.yml:
version: "3"
services:
psqldb:
build:
context: ....
- 21
0
votes
0
answers
129
views
failedmount for postgres statefulset kubernetes
Below is the code for postgres statefulset.
The ebs volume is getting attached but getting failed during mounting.
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: postgres
spec:
selector:
...
0
votes
2
answers
1k
views
Why does Postgres tell me "no pg_hba.conf entry for host..." when I have already modified this file?
The story so far:
I have installed Postgres on my Linux based desktop (HOST).
I have created a test database, with test data, on HOST.
I have added to the end of the pg_hba.conf file on HOST: host ...
- 103
1
vote
0
answers
735
views
If I have multiple versions of Postgres installed, how do I use a version specific tool?
For development reasons I have both Postgres 12 and 14 installed on my local machine. However, often I also need to use the pg_dump utility to create backups.
pg_dump 12 won't connect to a Postgres 14 ...
1
vote
1
answer
93
views
Compressing rarely queried historical data in postgres
In short: compress multiple historical DB records to bigger, compressed records and temporarily uncompress it to the original records for easier querying, and clean that up when querying is done. ...
- 161