RE: Postgres Point in time Recovery (PITR),
Hello, I am trying to setup barman for my test environment with the following steps but I m not able to start the backup. Please advice ! Postgres 11.2 streaming replication on docker container , Centos Server A : pg1 (primary) Server B : pg2 (Standby) I am using pg2 as a Barman server. Barman 2.8 is installed on pg2 Also I have installed " yum install barman-cli-2.8-1.el6.noarch.rpm" on pg1 (postgres server) for 'barman-wal-archive. @pg1 1 . Connect on the server pgsql and log into the postgres account: sudo -i -u postgres 2 . createuser --interactive -P barman 3 . createuser barman 4 . Edit postgresql.conf , for listen_addresses = '*' and sudo service postgresql restart wal_level = replica archive_mode = on archive_command = 'barman-wal-archive pg2 pgsql %p' 5. hostall all pg2/32 trust @pg2 (Barman Server) 1 . switch to user barman and generate the keys: ssh-keygen -t rsa 2 . Copy the key to the user account postgres on pgsql: ssh-copy-id postgres@pg1 3 . Barman also requires access to the postgres account on the server pg2. Copy the key into the directory of the postgres user and test the connection: ssh-copy-id postgres@localhost ssh postgres@localhost -C true 4 . Once this is done, log in as postgres user on pg1and generate an SSH key: ssh-keygen -t rsa 5 . Copy the generated key to the list of authorized keys of the user barman on pg2: ssh-copy-id barman@pg2 6 . Test the connection to the server: @barmaner server /etc/barman.d/pgsql.conf [test] ; Human readable description description = "Example of PostgreSQL Database (via SSH)" ssh_command = ssh postgres@pg1 conninfo = host=pg1 user=barman dbname=postgres backup_method = rsync ;reuse_backup = link ; Identify the standard behavior for backup operations: possible values are ; exclusive_backup (default), concurrent_backup ; concurrent_backup is the preferred method with PostgreSQL >= 9.6 backup_options = exclusive_backup ; Number of parallel workers to perform file copy during backup and recover ;parallel_jobs = 1 archiver = on ;archiver_batch_size = 50 barman@vipostgres-db-test:/etc/barman.d$ barman check pgsql WARNING: No backup strategy set for server 'pgsql' (using default 'exclusive_backup'). WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning. Server pgsql: WAL archive: FAILED (please make sure WAL shipping is setup) PostgreSQL: FAILED directories: OK retention policy settings: OK backup maximum age: OK (no last_backup_maximum_age provided) compression settings: OK failed backups: OK (there are 0 failed backups) minimum redundancy requirements: OK (have 0 backups, expected at least 0) ssh: FAILED (Connection failed using 'ssh postgres@vipostgres-cn-test -o BatchMode=yes -o StrictHostKeyChecking=no' return code 255) not in recovery: OK pg_receivexlog: FAILED pg_receivexlog compatible: FAILED (PostgreSQL version: None, pg_receivexlog version: None) receive-wal running: FAILED (See the Barman log file for more details) archiver errors: OK NOte: If I try to login mannulay on postgres from barman then it is aking for password : ssh postgres@pg1 Failed to add the host to the list of known hosts (/tmp/.ssh/known_hosts). == Authorized Access Notice == This computer system is the property of Exponential Interactive Inc. Activities are actively monitored and unauthorized access or use of this computer system is prohibited. == Password: Thanks, -Original Message- From: Andreas Kretschmer Sent: Friday, October 18, 2019 12:38 PM To: pgsql-general@lists.postgresql.org; Daulat Ram ; pgsql-general@lists.postgresql.org Subject: Re: Postgres Point in time Recovery (PITR), On 18 October 2019 07:59:21 CEST, Daulat Ram wrote: >Hello All, >Can you please share some ideas and scenarios how we can do the PITR in >case of disaster. > > >Thanks, Consider Barman. -- 2ndQuadrant - The PostgreSQL Support Company 2019-11-11 02:05:02,204 [10832] barman.wal_archiver INFO: No xlog segments found from file archival for pgsql. 2019-11-11 02:05:02,204 [10833] barman.postgres WARNING: Error retrieving PostgreSQL status: could not connect to server: Connection refused 2019-11-11 02:05:02,205 [10832] barman.wal_archiver INFO: No xlog segments found from streaming for pgsql. 2019-11-11 02:05:02,205 [10833] barman.server ERROR: ArchiverFailure:failed opening the PostgreSQL streaming connection for server pgsql 2019-11-11 02:05:02,206 [10834] barman.wal_archiver INFO: No xlog segments found from file archival for test. 2019-11-11 02:06:01,292 [10847] barman.backup_executor WARN
Partition, inheritance for storing syslog records.
Hello. I beg for the community ideas on how to do in the best way. I use centrally storing syslog messages in Postgres. All devices send syslog messages to a server (or could be several in fault tolerance) which use rsyslog to store the logs in the DB (currently I'm using mysql and postgre storing same entries). In time obvious the table grows. While now I have about 50GB of data, I want to plan for future storing and retrieving logs when needed to investigate something. I would not explain the columns of the syslog table as it is standard from the rsyslog provided info. A while I ago on Postgre 8.4 (I know it old and planning to move to latest release) I implemented inheritance, when creating main table and make inheritable tables using checks on syslogtag column to store logs from some specific applications (like dhcpd, smbd, tftpd) in separate tables. And created table rules on main table to place the incoming entries in respective table. CREATE TABLE syslog_dhcpd ( CONSTRAINT syslog_dhcpd_pkey PRIMARY KEY (id), CONSTRAINT syslog_dhcpd_message_unique UNIQUE (devicereportedtime, facility, priority, fromhost, message, syslogtag), CONSTRAINT syslog_dhcpd_syslogtag_check CHECK (syslogtag::text ~~ 'dhcpd%'::text) ) INHERITS (syslog); CREATE OR REPLACE RULE syslog_dhcpd_insert_rule AS ON INSERT TO syslog WHERE new.syslogtag::text ~~ 'dhcpd%'::text DO INSTEAD INSERT INTO syslog_dhcpd (customerid, receivedat, devicereportedtime, facility, priority, fromhost, message, ntseverity, importance, eventsource, eventuser, eventcategory, eventid, eventbinarydata, maxavailable, currusage, minusage, maxusage, infounitid, syslogtag, eventlogtype, genericfilename, systemid) VALUES (new.customerid, new.receivedat, new.devicereportedtime, new.facility, new.priority, new.fromhost, new.message, new.ntseverity, new.importance, new.eventsource, new.eventuser, new.eventcategory, new.eventid, new.eventbinarydata, new.maxavailable, new.currusage, new.minusage, new.maxusage, new.infounitid, new.syslogtag, new.eventlogtype, new.genericfilename, new.systemid); As this is was the only way to make partitioning on the version 8.4 (alternatively is using triggers), but it is not quite good on expanding. My goal is to make partitioning in such way: Partition by syslogtag so every application will go to separate partition. Then sub-partition each partition by fromhost (there will be about 10 different hosts that I want to be separate, others should go on main). Then sub-sub-partition by year. There could be not sub-partition by fromhost. Only by syslogtag, then by year. So how could be this accomplished both in version 8.4 and in version 12. Other ideas are welcome. The main goal is to be able to quickly investigate logs from some applications and some host searching regex in `message` column.
security on user for replication
We use replication with publication/subsctription. It´s ok, works fine. But if I go to my replica server and do select * from pg_subscription on field subconninfo I have all properties to connect. host, port, user, password and dbname, all these info are available. Documentation says user for replication is equivalent to a superuser and must have the login attribute. If this user has all this power and using that select on replica all that info is available ... How can I hide that info from users which are connected to my replica server or If it´s possible to have a replication user with not superuser rights or with NoLogin -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: security on user for replication
Am 11.11.19 um 14:26 schrieb PegoraroF10: How can I hide that info from users which are connected to my replica server you can use a .pgpass - file, see the documentation. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Rules documentation example
Hello, I'm reading the docs about the Postgres Rule system here: https://www.postgresql.org/docs/12/rules-views.html That page says: > It turns out that the planner will collapse this tree into a two-level query > tree: the bottommost SELECT commands will be “pulled up” into the middle > SELECT since there's no need to process them separately. But the middle > SELECT will remain separate from the top, because it contains aggregate > functions. If we pulled those up it would change the behavior of the topmost > SELECT, which we don't want. But I don't see an aggregate function. Is it referring to MIN? But that is this two-param version defined on the same page. It isn't an aggregate: CREATE FUNCTION min(integer, integer) RETURNS integer AS $$ SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END $$ LANGUAGE SQL STRICT; Is that an error in the docs, or am I missing something? Does a non-aggregate function also prevent the subqueries from being pulled up? Will all levels of that query actually get combined, or does something else prevent it? Thanks, Paul
Re: Rules documentation example
Paul A Jungwirth writes: > I'm reading the docs about the Postgres Rule system here: > https://www.postgresql.org/docs/12/rules-views.html > That page says: >> It turns out that the planner will collapse this tree into a two-level query >> tree: the bottommost SELECT commands will be “pulled up” into the middle >> SELECT since there's no need to process them separately. But the middle >> SELECT will remain separate from the top, because it contains aggregate >> functions. If we pulled those up it would change the behavior of the topmost >> SELECT, which we don't want. > But I don't see an aggregate function. Is it referring to MIN? Perhaps. Digging in the git history, that text seems to be mine (commit 1045304a3), but the example that it's talking about was pre-existing. I think I might've just misread it. It's also likely (assuming that I was documenting a behavior that I actually saw at the time) that the real issue is that MIN(), as presented, defaults to being volatile which would also prevent such flattening. But this example is so old that I'm not sure whether that particular optimization behavior existed then. I'm inclined to: (1) get rid of the example's MIN() function in favor of using LEAST(), which is standard and less confusing; (2) change the text to just say that the planner flattens these subqueries, so we don't pay any execution-time penalty from the way the view replacements are handled. regards, tom lane
Re: security on user for replication
## PegoraroF10 (mar...@f10.com.br): > How can I hide that info from users which are connected to my replica server https://www.postgresql.org/docs/current/catalog-pg-subscription.html Access to the column subconninfo is revoked from normal users, because it could contain plain-text passwords. Else: SSL certificates, pgpass file, or rig up some kerberos (that's not that elegant in this case). Regards, Christoph -- Spare Space.
