2830
English speaking PostgreSQL public chat. This group is for discussions on PostgreSQL-related topics and strives to provide best-effort support as well.
Hey teams, I have deployed postgres as a docker container on ubuntu linux,
some developer dropped the DB using ddl auto create, is there a way to recover the database?
Was mostly referring to https://www.postgresql.org/docs/current/ddl-schemas.html (you could think of them as subdirectories or namespaces for tables, useful for keeping it clean).
Читать полностью…
That sounds pretty reasonable.
Be aware that your database is still open to any other role ("user") until you REVOKE ALL ON DATABASE ... FROM public;
Really depends on what you want to do, and forgive me if you know all this already and were just concerned about the user aspect.
Читать полностью…
You might want to have a separate schema for them, though, rather than dump their stuff into public. You can set up their search_path to be "$user, public" if it isn't already and it will access public if not found in their own schema.
Читать полностью…
I really don't like the idea of having multiple OS users for each postgresql user.
Читать полностью…
Please help me create the statefullset for postgres in kubernetes with replication n auto failover (HA)
Читать полностью…
Hi Guys
Is there any script or something you guys know of. That script should scan my database, and see what are the columns that will be helpful to index but aren't indexed and show me them. I remember this was present as some script in sql server, but I want to know for postgresql is there is such
Wait, are we talking about "idle in transaction" or "idle" sessions?
Читать полностью…
Those have the bad habit of immediately coming back. Talk to your developers about connection pool sizing instead.
Читать полностью…
We are using Aurora postgres databases.. In the database i can see lot of idle transactions.. Is it okay? Should i delete?
Читать полностью…
is very small hackathon(<$10k), but came 400+ participants, i have suspicion that organisers have problems of scale to determinate the winner (announced only 3 judges) 100+ works per judge is 2-3 weeks of work
Читать полностью…
ooh i see. that's a thing to keep in mind when i deploy to public;
Читать полностью…
Sorry, I don't really understand what you're saying (it's the first time i'm setting up a postgressql server myself), but based on official documentation here's what i did:
the server OS is Debian, postgresql is installed from official debian repos. upon installation it creates a OS user called postgres. all the postgresql tools and the server processes are started under this user.
i've created PostgreSQL user (not OS user) using createuser command, and a database for that user using createdb command.
next i've added following line to /etc/postgres/17/main/pg_hba.conf
host all all 10.0.2.0/24 scram-sha-256
listen_address=10.0.2.1 to /etc/postgres/17/main/postgresql.conf, so postgres is not accessible from external network at all. the application for this database will be running on the same server, but i need access to the database over the internal network for development
Читать полностью…
It can even be e.g. "$user, viewer" and they are member of a viewer role which has views selecting from public or another app schema rather than public etc. (But, bear in mind you can't actually hide the structure of the database without locking down the system catalog tables which IIRC is not supported.)
Читать полностью…
never mind, found this in official documentation:
PostgreSQL database user names are logically separate from user names of the operating system in which the server runs. If all the users of a particular server also have accounts on the server's machine, it makes sense to assign database user names that match their operating system user names. However, a server that accepts remote connections might have many database users who have no local operating system account, and in such cases there need be no connection between database user names and OS user names.Читать полностью…
What is the "proper" way to create new users and databases? I see conflicting tutorials online, some of them create a system (OS) user for a new postresql user, like here: https://wiki.debian.org/PostgreSql. others do not mention any os user and just create users and databases via postgresql's superuser
Читать полностью…
AFAIK PostgreSQL doesn't track how often a certain column appears in a WHERE. A naïve idea would be to check your pg_stat_user_tables for significant SeqScan counts, then get the list of columns and query pg_stat_statements for queries havingquery ILIKE relname || ' WHERE %' || colname
orquery ILIKE relname || ' ON %' || colname
You get the idea...
I suppose that he refer to idle in transactions which are dangerous because they don't allow autovacuum to do its job
Читать полностью…
It's not a good thing and something you should grt you app developers to fix. It implies your app is starting a transaction and then not ending it before returning it to some form of connection pool.
Читать полностью…
Yes, I refuse. They do not even capitalise PostgreSQL consistently!
Читать полностью…
Well, if you insert twice NOW (after the glibc switch) they will match each other.
Create a new table abc(LIKE orig_table including all);
Insert into abc select * from orig_table;
see if those inserts fail, if they do, it is LIKELY the glibc issue.
Hi everyone,
I participated in two hackathons on Devpost:
The Syrotech MVP Hackathon 2025
Eco-Innovation Challenge
The winner announcements were supposed to be on September 1st and 6th, respectively, but I still don’t see any winners posted.
Does anyone know if Devpost is legit or if this might be a scam?