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.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
40