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,969
questions
166
votes
10
answers
437k
views
GRANT SELECT to all tables in postgresql
Is there a one-liner that grants the SELECT permissions to a new user postgresql?
Something that would implement the following pseudo-code:
GRANT SELECT ON TABLE * TO my_new_user;
1
vote
1
answer
8k
views
Got django.db.utils.OperationalError: could not connect to server: Connection refused
I found a Django project and failed to get it running in Docker container in the following way:
git clone git clone https://github.com/NAL-i5K/django-blast.git
$ cat requirements.txt in this files ...
2
votes
1
answer
4k
views
PostgreSQL Service status Active(Exited) but I am able to connect
I have installed PostgreSQL (9.5) on a new Ubuntu (16.04) VM on GCD. THe status of service is active(exited). The output of sudo systemctl status postgresql is:
● postgresql.service - PostgreSQL ...
0
votes
0
answers
14
views
CPU/Threads during the scraping process
I am a junior developer and have a question about performance in scraping. I noticed that optimizing the script for software, for example, scraping Google and inserting data into PostgreSQL, is not ...
0
votes
0
answers
29
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 ...
1
vote
2
answers
3k
views
Can't start postgresql service
I'm trying to start the service but it isn't starting... It says:
[....] Starting PostgreSQL 9.6 database server: main[....] The PostgreSQL server failed to start. Please check the log output: 2016-...
1
vote
2
answers
5k
views
Are connections to databases http based or tcp based
are connections to Database http based or tcp based.
Are jdbc of spring-boot application to postgressql http method connections based or tcp based connections.
15
votes
1
answer
35k
views
Postgresql 13 - Speed up pg_dump to 5 minutes instead of 70 minutes
We use pg_dump nightly to make a snapshot of our database. We did for a long time with a simple command
pg_dump -Fc database_name
This takes about an hour and produces a file of 30+GByte.
How can we ...
4
votes
1
answer
7k
views
Connect to postgres database behind nginx reverse-proxy using docker containers
I have set up a postgres database container and can connect and query it directly using its published port 5434.
The goal is to deploy the test database later and access it via nginx reverse proxy.
I ...
8
votes
2
answers
11k
views
How can I connect to Postgres running on the Windows host from inside WSL2?
I have Postgres running on my windows machine and I need to connect to it from inside WSL2.
The postgres windows service is running
I can connect to it from the windows command line using psql
The IP ...
2
votes
1
answer
6k
views
Postgresql Service not Starting
I'm trying to start a PostgreSQL 10.10 server on an Armbian (Ubuntu Bionic with Armbian Linux 4.14.133-odroidxu4) device (Odroid Xu4). The server was previously working, but after a power failure, it ...
2
votes
2
answers
2k
views
Flush PostgreSQL log files (Close & reopen files) without restarting postgresql?
Is it possible to get PostgreSQL to flush it's log files and reopen them, without having to restart? I have tried to send the HUP signal, but that doesn't do it. I accidentally have a large postgres ...
0
votes
1
answer
131
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
1
answer
377
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....
25
votes
2
answers
15k
views
Django: CONN_MAX_AGE persists connections, but doesn't reuse them with PostgreSQL
I've got a django setup are using Django 1.6.7 and Postgres 9.3 on Ubuntu 14.04 LTS.
At any given time, the site gets about ~250 simultaneous connections to the PostgreSQL database, which is a Quad ...
0
votes
2
answers
7k
views
Postgres Remote Connection Timeout
I've been reading tutorials extensively to help figure out my problem, but to no avail.
I have a Redhat VM that I've installed Postgres on that I'm trying to make available for remote connections. ...
18
votes
4
answers
15k
views
EXPLAIN with index suggestions
Is there any (preferably open source) software than can analyze a PostgreSQL EXPLAIN, and recommend the necessary indices that would speed up the query?
0
votes
2
answers
163
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 ...
2
votes
0
answers
71
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 ...
5
votes
3
answers
8k
views
How to disable 1 version of PostgreSQL server without uninstalling it
I have installed PostgreSQL 9.1 and 9.2 on my Ubuntu machine. The postgresql service starts both versions:
$ sudo service postgresql start
$ * Starting PostgreSQL 9.1 database server [ OK ]
$ * ...
3
votes
3
answers
976
views
Best way to migrate from MySQL to PostgreSQL
I have to migrate a MySQL database to PostgreSQL. I have tried the mysqldump with --compatible = postresql. I have seen that some poeple use ETL like Talend to make the migration but you must describe ...
0
votes
2
answers
6k
views
Can't connect to a postgres server
I have a strange situation, I have created a second postgres server that will eventually become a slave to my current master, anyway for testing purposes I have currently installed postgres, and ...
30
votes
4
answers
44k
views
How can I show the content of a trigger with psql?
I know I can list the triggers with \dft. But how can I see one concrete trigger? I want to know details like on which events the trigger is executed, which function is executed and so on.
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.
5
votes
2
answers
5k
views
Can one check available cores at runtime on postgres?
TL; DR: Is there anyway to query a postgres instance for how many cores it has available for processing?
Postgres has a good reputation with respect to parallelizing multiple queries efficiently ...
3
votes
1
answer
5k
views
Slot replication not working in Postgres 9.4
I am using Postgres 9.4 on Windows 2008 server. There are three such instances that I am using. One as master and the rest of the two are slave/standby. The version of Postgres is 9.4 in all the three ...
1
vote
2
answers
3k
views
pg_dump complains PostgreSQL version 11 is not installed after installing postgres-client-13
We just upgraded our PostgreSQL servers to v13. We heavily use pg_dump on Ubuntu 18 systems to transfer data between databases. After upgrading the servers, pg_dump would complain about a version ...
0
votes
1
answer
12k
views
pg_ctl: could not start server
I am trying to start the PostgreSQL server installed on a remote Redhat server(RHEL 6.7 x86_64) through putty.
The command that I used to start the server is /etc/init.d/postgresql-9.5 start
But it ...
0
votes
0
answers
49
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 ...
0
votes
1
answer
7k
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-...
6
votes
1
answer
18k
views
Tons of "invalid command \N" when restoring a dump
I'm following these instructions to upgrade postgres from 9.1 to 9.3:
http://www.postgresql.org/docs/9.1/static/upgrading.html
When I restore (Step 8), I get a ton of messages like this:
psql:/mnt/...
0
votes
0
answers
125
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
6k
views
pgadmin4 : server won't start
I have this error message trying to start pgadmin4 web interface from the desktop helper on fedora 28/postgresql 10 :
Failed to launch the application server, server thread exiting.
Though, it ...
4
votes
1
answer
11k
views
Recover PostgreSQL database from filesystem backup
I have a PostgreSQL 8.3 data directory backup. I need to copy a database from this backup into a new PostgreSQL instance. Due to problems with the old server I cannot do a pg_dump of the database.
...
0
votes
2
answers
272
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 ...
0
votes
2
answers
108
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
2
answers
666
views
PostgreSQL Log Rotation Size Reached File Limit
I have configured the following settings in PostgreSQL 13 on Windows.
logging_collector = on
log_rotation_size='100MB'
log_truncate_on_rotation = on
log_filename ='postgresql-%Y-%m-%d.log'
I ...
2
votes
1
answer
2k
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 ...
0
votes
2
answers
7k
views
Postgres /root": Permission denied
why on ubuntu 14.10 every time that i try to start the postgres on:
sudo -p 'sudo password: ' -u postgres /usr/lib/postgresql/9.4/bin/initdb -D /mnt/postgresql/9.4/data
show this:
could not ...
0
votes
3
answers
1k
views
having issues while accessing Postgresql
I have installed postgreSql client 9.6 following this link postgreSql installation and I am using centos version 7 on Linux.
It got installed successfully but I am getting some problems while ...
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 ...
18
votes
4
answers
47k
views
How do a I edit .conf file for a postgres AWS RDS?
I would like to alter pg_hba.conf in my postgres RDS instance so that I can use cert authentication. I have found the Parameters Group manager in the RDS which allows me to alter parameters but it ...
0
votes
1
answer
633
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
100
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 ...
52
votes
5
answers
80k
views
Unable to connect to public PostgreSQL RDS instance
I created a basic test PostgreSQL RDS instance in a VPC that has a single public subnet and that should be available to connect over the public Internet. It uses the default security group, which is ...
0
votes
0
answers
29
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 -...
3
votes
2
answers
14k
views
Why can't I grant select privileges to all table in schema with PostgreSQL?
I have a database my_database, and it have some tables named my_table_1, my_table_2, ... , my_table_128 under schema public. I need to grant select privileges for all tables in schema public to user ...
0
votes
0
answers
71
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
953
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 ...