Re: Database connection log
I believe that you can achieve what you want with the following configuration parameters: log_connections and log_line_prefix PostgreSQL: Documentation: 10: 19.8. Error Reporting and Logging | | | | | | | | | | | PostgreSQL: Documentation: 10: 19.8. Error Reporting and Logging | | | Regards, Pierre On Thursday, June 14, 2018, 4:48:54 PM GMT+2, Tiffany Thang wrote: Hi,Does PostgreSQL keep a log of client connections to the database like Oracle's listener.log? I would like to extract information such as how many connections are made to the database daily, the IP addresses they originated from and the schemas they are connected to. Would it be possible to extract the above information in PostgreSQL? Thanks.
using pg_basebackup for point in time recovery
Hi,I find the documentation about pg_basebackup misleading : the documentation states that standalone hot backups cannot be used for point in time recovery, however I don't get the point : if one has a combination of the nightly pg_basebackup and the archived wals, then it is totally OK to do point in time I assume ? (of course the recovery.conf must be manually changed to set the restore_command and the recovery target time) Here is the doc, the sentence that I find misleading is "There are backups that cannot be used for point-in-time recovery", also mentioning that they are faster than pg_dumps add to confusion (since pg_dumps cannot be used for PITR)Doc: https://www.postgresql.org/docs/current/static/continuous-archiving.html It is possible to use PostgreSQL's backup facilities to produce standalone hot backups. These are backups that cannot be used for point-in-time recovery, yet are typically much faster to backup and restore than pg_dump dumps. (They are also much larger than pg_dump dumps, so in some cases the speed advantage might be negated.) As with base backups, the easiest way to produce a standalone hot backup is to use the pg_basebackup tool. If you include the -X parameter when calling it, all the write-ahead log required to use the backup will be included in the backup automatically, and no special action is required to restore the backup. Thanks and regards, Pierre On Tuesday, June 19, 2018, 1:38:40 PM GMT+2, Ron wrote: On 06/15/2018 11:26 AM, Data Ace wrote: Well I think my question is somewhat away from my intention cause of my poor understanding and questioning :( Actually, I have 1TB data and have hardware spec enough to handle this amount of data, but the problem is that it needs too many join operations and the analysis process is going too slow right now. I've searched and found that graph model nicely fits for network data like social data in query performance. If your data is hierarchal, then storing it in a network database is perfectly reasonable. I'm not sure, though, that there are many network databases for Linux. Raima is the only one I can think of. Should I change my DB (I mean my DB for analysis)? or do I need some other solutions or any extension? Thanks -- Angular momentum makes the world go 'round.
Re: Is there a way to be notified on the CREATE TABLE execution?
I believe you could use an event trigger in postgres to capture the fact that a table was created: https://www.postgresql.org/docs/current/static/event-triggers.html In the trigger you would then have to code whatever is needed to notify the external software (via a REST call or by posting something in a messaging bus, ...) Regards, Pierre On Wednesday, June 20, 2018, 12:08:48 AM GMT+2, Igor Korot wrote: Hi, ALL, Consider a scenario: 1. A software that uses libpq is executing. 2. Someone opens up a terminal and creates a table. 3. A software needs to know about this new table. I presume this is a DBMS-specific... Thank you.
Re: using pg_basebackup for point in time recovery
Hi Michael Thanks for the confirmation. Your rewording removes the confusion. I would maybe take the opportunity to re-instate that pg_dump cannot be used for PITR, so in the line of "These are backups that could be used for point-in-time recovery if combined with a WAL archive able to recover up to the wanted recovery point. These backups are typically much faster to backup and restore than pg_dump for large deployments but can result as well in larger backup sizes, so the speed of one method or the other is to evaluate carefully first. Consider also that pg_dump backups cannot be used for point-in-time recovery." Maybe the confusion stems from the fact that if you restore a standalone (self-contained) pg_basebackup then - by default - recovery is done with the recovery_target immediate option, so if one needs point-in-time recovery he has to edit the recovery.conf and brings the archives.. Thanks and regards, Pierre On Wednesday, June 20, 2018, 5:38:56 AM GMT+2, Michael Paquier wrote: Hi Pierre, On Tue, Jun 19, 2018 at 12:03:58PM +0000, Pierre Timmermans wrote: > Here is the doc, the sentence that I find misleading is "There are > backups that cannot be used for point-in-time recovery", also > mentioning that they are faster than pg_dumps add to confusion (since > pg_dumps cannot be used for PITR): > https://www.postgresql.org/docs/current/static/continuous-archiving.html Yes, it is indeed perfectly possible to use such backups to do a PITR as long as you have a WAL archive able to replay up to the point where you want the replay to happen, so I agree that this is a bit confusing. This part of the documentation is here since the beginning of times, well 6559c4a2 to be exact. Perhaps we would want to reword this sentence as follows: "These are backups that could be used for point-in-time recovery if combined with a WAL archive able to recover up to the wanted recovery point. These backups are typically much faster to backup and restore than pg_dump for large deployments but can result as well in larger backup sizes, so the speed of one method or the other is to evaluate carefully first." I am open to better suggestions of course. -- Michael
Re: using pg_basebackup for point in time recovery
Hi Michael On Thursday, June 21, 2018, 7:28:13 AM GMT+2, Michael Paquier wrote: >You should avoid top-posting on the Postgres lists, this is not the >usual style used by people around :) Will do, but Yahoo Mail! does not seem to like that, so I am typing the > myself >Attached is a patch which includes your suggestion. What do you think? >As that's an improvement, only HEAD would get that clarification. Yes I think it is now perfectly clear. Much appreciated to have the chance to contribute to the doc by the way, it is very nice >Perhaps. There is really nothing preventing one to add a recovery.conf >afterwards, which is also why pg_basebackup -R exists. I do that as >well for some of the framework I work with and maintain. I just went to the doc to check about this -R option :-) Pierre
Re: Setting up continuous archiving
Hello
What you are doing is called "log shipping", which means that when a wal
(write-ahead log) is filled in on the database server you ship it to a backup
server via rsync. It is fine but as you said the disadvantage is that the file
is shipped only when it is full, so you could have data loss (the last wal not
shipped)
A more modern and easy way is to use streaming replication: in this case the
logs are streamed continuously to the standby or to the backup server (one can
use streaming replication without implementing a standby database). Look at the
doc on the next page than the one you referred to
(https://www.postgresql.org/docs/9.3/static/high-availability.html)
There is a nice tool that does one you plan to do (rsync of archived file) but
also the more modern way (streaming replication): it is called barman:
https://www.pgbarman.org/. You should probably use their tool but you can also
read the doc to get the concepts and some ideas
Rgds, Pierre
On Wednesday, September 26, 2018, 9:21:29 AM GMT+2, Yuri Kanivetsky
wrote:
Hi,
I'm trying to compile a basic set of instruction needed to set up
continuous archiving and to recover from a backup. I'm running
PostgreSQL 9.3 on Debian Stretch system.
I've got a database and a backup servers. The idea is to set up WAL
archiving, and occasionally do full (base) backups. A base backup plus
WAL segment files from that point onward must provide enough
information to restore database to the latest state. Except for a
window for data loss that is caused by the fact that WAL segment files
aren't transferred momentarily, and more importantly that WAL segment
files are only transferred upon being filled.
---
Setting up continuous archiving
* Set up WAL archiving
* on backup server under postgres user
* create /var/lib/postgresql/wal_archive dir
$ mkdir /var/lib/postgresql/wal_archive
* on database server under postgres user
* generate ssh key
$ ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa # providing
path to key file makes it
# to not ask questions
* add corresponding record to known_hosts file
$ ssh-keyscan -t rsa BACKUP_SRV >> ~/.ssh/known_hosts
* locally
* authorize login from database to backup server
$ ssh DATABASE_SRV cat ~/.ssh/id_rsa.pub | ssh BACKUP_SRV
'mkdir --mode 0700 .ssh; cat >> ~/.ssh/authorized_keys; chmod 0600
.ssh/authorized_keys'
* on database server under root
* change postgresql.conf
wal_level = archive
archive_mode = on
archive_command = 'rsync -a %p
BACKUP_SRV:/var/lib/postgresql/wal_archive/%f'
* restart PostgreSQL
# systemctl resart postgresql
* Make a base backup
* on database server under root
* add a line to postgresql.conf
max_wal_senders = 1
* add a line to pg_hba.conf
host replication replication BACKUP_SRV_IP/BACKUP_SRV_NETMASK trust
* restart PostgreSQL
# systemctl restart postgresql
* on database server under postgres user
* create replication user
CREATE USER replication WITH REPLICATION;
or
$ createuser --replication replication
* on backup server under postgres user
* make base backup
$ pg_basebackup -h DATABASE_SRV -U replication -D
/var/lib/postgresql/base_backups/$(date +%Y%m%d-%H%M%S)
Restoring from a backup
* under root
* stop PostgreSQL if running
# systemctl stop postgresql
* under postgres user
* move data dir
$ mv 9.3{,-$(date +%Y%m%d-%H%M%S)}
* copy backup
$ mkdir 9.3
$ cp -r base_backups/TIMESTAMP 9.3/main
* copy unarchived segment files
$ find 9.3-TIMESTAMP/main/pg_xlog -maxdepth 1 -type f -exec cp -t
9.3/main/pg_xlog {} +
* create recovery.conf in 9.3/main
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
* under root
* start PostgreSQL
# systemctl start postgresql
A few notes.
Running out of space on backup server can lead in its turn to database
server running out of space, since WAL segment files stop being
archived and keep piling up. The same might happen when archiving
falls behind. Which also widens the window for data loss.
WAL archiving doesn't track changes to configuration files.
pg_basebackup will back up configuration files only if they are inside
data dir.
If database doesn't generate much WAL traffic, there could be a long
delay between completing a transaction and archiving its results
(archive_timeout).
You might want to prevent users from accessing the database until
you're sure the recovery was successful (pg_hba.conf).
I'm not considering here possible issues with tablespaces and other caveats:
https://www.postgresql.org/docs/9.3/static/continuous-archiving.html#CONTINUOUS-ARCHIVING-CAVEATS
---
Most importantly, does it makes sense to keep more than one base
Re: Split read/write queries between 2 servers (one master and one slave with streaming replication)?
pgpool does just that: it enables you to use the standby database as a read only while other queries are sent to the primary database only (http://www.pgpool.net/). Good product and good support (on the list° This looks a very interesting possibility, although it is more related to automated failover than to load balancing of read only queries : http://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/, depending on your client it might be supported. It is not supported - yet ? - by the node-postgres driver. Regards, Pierre On Wednesday, February 14, 2018, 3:29:03 PM GMT+1, Venkateswaran wrote: Hi, I am also trying to split read and write queries, where reads should go to the slave server (streaming replication) and writes to the master server. Any tool available to perform this activity instead of two connection pool? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Split read/write queries between 2 servers (one master and one slave with streaming replication)?
Thanks. Indeed pgBouncer is the usual candidate in front of postgres streaming replication, together with pgpool. Take care that your link on pgBouncer dates back from 2008 !. I had a look at pgBouncer in the past, but it was not actively maintained at that time. So I settled on a combination of pgpool and repmgr. People on pgpool mailing list are very reactive and helpful and the doc on the project is continuously improving. Pierre On Wednesday, February 14, 2018, 8:44:47 PM GMT+1, Melvin Davidson wrote: On Wed, Feb 14, 2018 at 2:27 PM, Pierre Timmermans wrote: pgpool does just that: it enables you to use the standby database as a read only while other queries are sent to the primary database only (http://www.pgpool.net/). Good product and good support (on the list° This looks a very interesting possibility, although it is more related to automated failover than to load balancing of read only queries : http://paquier.xyz/ postgresql-2/postgres-10- libpq-read-write/, depending on your client it might be supported. It is not supported - yet ? - by the node-postgres driver. Regards, Pierre On Wednesday, February 14, 2018, 3:29:03 PM GMT+1, Venkateswaran wrote: Hi, I am also trying to split read and write queries, where reads should go to the slave server (streaming replication) and writes to the master server. Any tool available to perform this activity instead of two connection pool? -- Sent from: http://www.postgresql-archive. org/PostgreSQL-general- f1843780.html Actually, if you are looking for load balancing PgBouncer is better than PgPool. There is a nice article about that. https://www.last.fm/user/Russ/journal/2008/02/21/zd_postgres_connection_pools:_pgpool_vs._pgbouncer I used PgBouncer in a few of ny previous positions and found it to be very fast and efficient. -- Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
