ubuntu 18: PostgreSQL does not start. how can I totally remove and reinstall it
Hi there I made somehow a mess with my PostgreSQL installation an an ubuntu 18.4 with PostgreSQL 10.0 service postgresql status ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Sat 2021-01-23 21:21:13 CET; 2min 26s ago Process: 1853 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 1853 (code=exited, status=0/SUCCESS) I would like to remove everything, and reinstall. How can I do that? It seems, that apt remove --purge postgresql apt remove --purge postgresql-client I not enough, as I have the same situation afterwards. Or is there a way that I can rebuild PostgreSQL datastructure? thanks Robert
solved (was plain stupidity) Re: ubuntu 18: PostgreSQL does not start. how can I totally remove and reinstall it
thanks a lot. why dos such stupidity not hurt. ? have a nice weekend robert On 24.01.21 08:04, Julien Rouhaud wrote: On Sun, Jan 24, 2021 at 2:58 PM rob...@redo2oo.ch wrote: root@elfero-test:~/scripts# pg_lsclusters Ver Cluster Port Status OwnerData directory Log file 10 main5433 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log [...] psycopg2.OperationalError: could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432? It looks like your instance is configured to listen on port 5433, not 5432.
how to set permission, so I can run pg_dumd in a cron job
Hi Friends I would like to have a cronjob creating a dump of a db. I am on a ubuntu 18 lts, potgres v10. I have a user robert with superuser db permission. a database "mydb" of which I would like to to a nightly dump. I tried to set thing in hba_conf like this: # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust and I create a /root/.pgpass file with this content hostname:port:database:username:password localhost:5432:mydb:robert: but still I get: root@elfero:~# pg_dump -U robert -d mydb > dumps/mydb.sql pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL: Peer authentication failed for user "robert" can you please give me a hand thanks robert
Re: how to set permission, so I can run pg_dumd in a cron job
Adrin, thanks On 13.03.21 17:23, Adrian Klaver wrote: On 3/13/21 8:16 AM, robert rottermann wrote: Hi Friends I would like to have a cronjob creating a dump of a db. I am on a ubuntu 18 lts, potgres v10. I have a user robert with superuser db permission. a database "mydb" of which I would like to to a nightly dump. I tried to set thing in hba_conf like this: # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust and I create a /root/.pgpass file with this content hostname:port:database:username:password localhost:5432:mydb:robert: but still I get: root@elfero:~# pg_dump -U robert -d mydb > dumps/mydb.sql pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL: Peer authentication failed for user "robert" can you please give me a hand 1) Did you have the server reload the conf files after making the changes? yes I did 2) Is there an entry for something like: local all all peer before the lines you show above? no I added the whol pg_conf at the end. There is one thing particulare with the installation. I have postgresql 9.5 and 10.0 on the box. now when I check pg_dump -V pg_dump (PostgreSQL) 9.5.24 root@elfero:~# ll /usr/bin/pg_dump lrwxrwxrwx 1 root root 37 Nov 13 2019 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper* when I switch to user postgres, and the us psql, I get the following: root@elfero:~# su postgres postgres@elfero:/root$ psql -d elfero could not change directory to "/root": Permission denied psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1), server 9.5.24) Type "help" for help. looks, as if I have a mess with this two instances. How can I best clean that up. I need only PostgreSQL 10 thanks again robert # PostgreSQL Client Authentication Configuration File # === # # Refer to the "Client Authentication" section in the PostgreSQL # documentation for a complete description of this file. A short # synopsis follows. # # This file controls: which hosts are allowed to connect, how clients # are authenticated, which PostgreSQL user names they can use, which # databases they can access. Records take one of these forms: # # local DATABASE USER METHOD [OPTIONS] # host DATABASE USER ADDRESS METHOD [OPTIONS] # hostssl DATABASE USER ADDRESS METHOD [OPTIONS] # hostnossl DATABASE USER ADDRESS METHOD [OPTIONS] # # (The uppercase items must be replaced by actual values.) # # The first field is the connection type: "local" is a Unix-domain # socket, "host" is either a plain or SSL-encrypted TCP/IP socket, # "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a # plain TCP/IP socket. # # DATABASE can be "all", "sameuser", "samerole", "replication", a # database name, or a comma-separated list thereof. The "all" # keyword does not match "replication". Access to replication # must be enabled in a separate record (see example below). # # USER can be "all", a user name, a group name prefixed with "+", or a # comma-separated list thereof. In both the DATABASE and USER fields # you can also write a file name prefixed with "@" to include names # from a separate file. # # ADDRESS specifies the set of hosts the record matches. It can be a # host name, or it is made up of an IP address and a CIDR mask that is # an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that # specifies the number of significant bits in the mask. A host name # that starts with a dot (.) matches a suffix of the actual host name. # Alternatively, you can write an IP address and netmask in separate # columns to specify the set of hosts. Instead of a CIDR-address, you # can write "samehost" to match any of the server's own IP addresses, # or "samenet" to match any address in any subnet that the server is # directly connected to. # # METHOD can be "trust", "reject", "md5", "password", "scram-sha-256", # "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert". # Note that "password" sends passwords in clear text; "md5" or # "scram-sha-256" are preferred since they send encrypted passwords. # # OPTIONS are a set of options for the authentication in the format # NAME=VALUE. The available options depend on the different # authentication methods -- refer to the "Client Authentication" # section in the documentation for a list of which opt
solved: Re: how to set permission, so I can run pg_dumd in a cron job
Thanks again Adrian On 13.03.21 18:37, Adrian Klaver wrote: On 3/13/21 9:25 AM, robert rottermann wrote: Adrin, thanks can you please give me a hand 1) Did you have the server reload the conf files after making the changes? yes I did 2) Is there an entry for something like: local all all peer before the lines you show above? no I added the whol pg_conf at the end. There is one thing particulare with the installation. I have postgresql 9.5 and 10.0 on the box. now when I check pg_dump -V pg_dump (PostgreSQL) 9.5.24 root@elfero:~# ll /usr/bin/pg_dump lrwxrwxrwx 1 root root 37 Nov 13 2019 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper* when I switch to user postgres, and the us psql, I get the following: root@elfero:~# su postgres postgres@elfero:/root$ psql -d elfero could not change directory to "/root": Permission denied psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1), server 9.5.24) Type "help" for help. looks, as if I have a mess with this two instances. How can I best clean that up. I need only PostgreSQL 10 From command line what does pg_lsclusters show? root@elfero:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.5 main 5432 online postgres /mnt/HC_Volume_8755337/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log 10 main 5433 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log Which pg_hba.conf file did you change? The one in: /etc/postgresql/9.5/main or /etc/postgresql/10/main To specify a version of pg_dump to use do something like: pg_dump --cluster 10/main -d some_db -U some_user -p some_port The important part is the some_port. You will need to use the one specified for the instance(cluster) as returned by the pg_lscluster command. I did in deed change the wrong file now, I need remove one of the two postgresql versions, but for this I will open a new question thanks and have a nice weekend Robert thanks again robert # Database administrative login by Unix domain socket #local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust q# IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5
how to best remove version 10 (and keep version 9.5)
Hi folks I am running an Ubuntu 18.04 lts remote box. For some (complicated) reasons I did install PostgreSQL 9.5 alongside the already installed V10 instance. I did set the data_directory to point some disk “outside” the standard /etc/postgresql/9.5/main structure. Now, what I would like to do is to get rid of one of the two installed PostgreSQL versions. Also there are aliasings like: /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper* I would like to avoid them if possible. Things are running now with version 9.5 so I tend to prefer to keep it. The whole setup is kind of temporary and will be migrated to the “real thing" during this year. So it is not a problem that the PostgreSQL version is dated. I would be grateful for your help robert
pgadmin4: not possible to create server
Hi there, I have installed pgadmin4 locally using its docker image. this i did running the following command: docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=admin" -e "PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4 I then could access it and log into it. However all my attempts end in: Unable to connect to server: could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432? can anybody give me a hint, what to do? thanks robert in /etc/postgresql/10/main/postgresql.conf i have: #-- # CONNECTIONS AND AUTHENTICATION #-- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; and this is my /etc/postgresql/10/main/pg_hba.conf # DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database superuser can access the database using some other method. # Noninteractive access to all databases is required during automatic # maintenance (custom daily cronjobs, replication, and similar tasks). # # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5
