Prune or Purge data stored on Postgres 14.13
Hi, I am running PostgreSQL 14.13 on RHEL 8.10 OS. Is there a way to Prune or Purge data stored on PostgreSQL 14.13 ? For example I have Analytics data stored in PostgreSQL 14.13 server for last 1 year (1st September 2023 till date) Is there a way to prune analytics data from Analytics Data stored on PostgreSQL 14.13 Database server starting from 1st September 2023 till February 29, 2024 and archive it to a tape drive or network file storage? (Six months analytics data). This is to make sure we do not run into an out of disk/storage space situation. Once we carry out the prune operation, we only have 6 months of data in the PG Database server and the older data beyond six months will be on tape drive or network file storage. Please guide me. Thanks in advance. Best Regards, Kaushal
Re: Migrating MySQL DB to PostgreSQL DB.
On Mon, Jul 31, 2023 at 4:15 AM Ron wrote: > On 7/30/23 09:01, Kaushal Shriyan wrote: > > Hi, > > > > I am running MySQL DB 8.0.34 on Red Hat Enterprise Linux release 8.7 > > (Ootpa). Is there a way to import MySQL DB along with data to PostgreSQL > > 14 database server? > > > > Please suggest. Thanks in advance. > > Depending on the size of the database, the "exoticness" of the datatypes, > the amount of downtime available and how complicated any stored procedures > and triggers are, simple CSV export/import might do the trick. > > -- > Born in Arizona, moved to Babylonia. > > Thanks Ron for the suggestion. Much appreciated. Are there any PostgreSQL GUI applications for MacOS, Windows and Linux similar to MySQL Workbench ( https://www.mysql.com/products/workbench/) ? Please suggest. Thanks in advance. Best Regards, Kaushal
Re: Migrating MySQL DB to PostgreSQL DB.
Thanks Ken and Adrian for the quick email response. Really appreciate it. Thanks once again👍 Best Regards, Kaushal On Sun, Jul 30, 2023 at 8:23 PM Adrian Klaver wrote: > On 7/30/23 07:11, Ken Marshall wrote: > > On Sun, Jul 30, 2023 at 07:31:02PM +0530, Kaushal Shriyan wrote: > >> Hi, > >> > >> I am running MySQL DB 8.0.34 on Red Hat Enterprise Linux release 8.7 > >> (Ootpa). Is there a way to import MySQL DB along with data to > PostgreSQL 14 > >> database server? > >> > >> Please suggest. Thanks in advance. > >> > >> Best Regards, > >> > >> Kaushal > > > > Hi Kaushal, > > > > Ora2Pg may work for you: > > > > https://ora2pg.darold.net/ > > There is also pgloader: > > https://pgloader.io/ > > > > > Regards, > > Ken > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Migrating MySQL DB to PostgreSQL DB.
Hi, I am running MySQL DB 8.0.34 on Red Hat Enterprise Linux release 8.7 (Ootpa). Is there a way to import MySQL DB along with data to PostgreSQL 14 database server? Please suggest. Thanks in advance. Best Regards, Kaushal
Re: Grant all privileges to user on a database
On Mon, Jul 24, 2023 at 6:51 PM Ron wrote:
>
> On 7/24/23 08:15, Kaushal Shriyan wrote:
> > Hi,
> >
> > I am running postgresql15-server 15.3 on Red Hat Enterprise Linux
> > release 8.7 (Ootpa)
> >
> > # rpm -qa | grep -i post
> > postgresql15-server-15.3-2PGDG.rhel8.x86_64
> > postgresql15-libs-15.3-2PGDG.rhel8.x86_64
> > postgresql15-15.3-2PGDG.rhel8.x86_64
> > #
> >
> > $psql
> > psql (15.3)
> > Type "help" for help.
> >
> > postgres=# GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;
> > postgres=# \du
> > List of roles
> >Role name | Attributes
> > | Member of
> >
--++---
> > cbdevdbadmin |
| {}
> > postgres | Superuser, Create role, Create DB, Replication, Bypass
RLS | {}
> >
> > postgres=#
> >
> > Am I missing something? Please suggest. Thanks in advance.
>
> No, you aren't. Role attributes are not the same as table privileges.
>
>
> --
> Born in Arizona, moved to Babylonia.
>
>
Thanks Ron for the quick response and appreciate it. I am currently facing
the below error while configuring the PostgreSQL 15.3 from Drupal CMS
version 9.5.10 (https://www.drupal.org/project/drupal/) web interface.
Failed to CREATE a test table on your database server with the command
CREATE TABLE {drupal_install_test} (id int NOT NULL PRIMARY KEY). The
server reports the following message: SQLSTATE[42501]: Insufficient
privilege: 7 ERROR: permission denied for schema public LINE 1: CREATE
TABLE "drupal_install_test" (id int NOT NULL PRIMARY ... ^: CREATE TABLE
"drupal_install_test" (id int NOT NULL PRIMARY KEY); Array ( ) .
Are you sure the configured username has the necessary permissions to
create tables in the database?
Please suggest. Thanks in advance.
Best Regards,
Kaushal
Grant all privileges to user on a database
Hi,
I am running postgresql15-server 15.3 on Red Hat Enterprise Linux
release 8.7 (Ootpa)
# rpm -qa | grep -i post
postgresql15-server-15.3-2PGDG.rhel8.x86_64
postgresql15-libs-15.3-2PGDG.rhel8.x86_64
postgresql15-15.3-2PGDG.rhel8.x86_64
#
$psql
psql (15.3)
Type "help" for help.
postgres=# GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;
postgres=# \du
List of roles
Role name | Attributes
| Member of
--++---
cbdevdbadmin || {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
Am I missing something? Please suggest. Thanks in advance.
Best Regards,
Kaushal
Re: Replication between Master PostgreSQL database version 9.6.1 and Standby/Slave PostgreSQL database version 10.17.
Thanks Christophe for the email and appreciate it. I am not sure if I understand the difference between binary and logical replication between PostgreSQL Master and Standby/Slave server. Please guide me. Thanks in advance. Best Regards, Kaushal On Wed, Oct 5, 2022 at 11:02 PM Christophe Pettus wrote: > > > > On Oct 5, 2022, at 09:32, Kaushal Shriyan > wrote: > > > > Hi, > > > > Is replication possible between PostgreSQL support Master database > running 9.6.1 version which is a lower version to Standby/Slave running > version 10.17? > > Binary replication is not possible between different major versions. You > can do logical replication, using the pglogical extension: > > https://github.com/2ndQuadrant/pglogical > > (Although not directly related, do note that 9.6 has been past end-of-life > for nearly a year, and version 10 will reach end-of-life in November of > this year. 10.17 is over a year old, and 9.6.1 is almost six years old; > the most recent versions of each are 10.22 and 9.6.24.)
Replication between Master PostgreSQL database version 9.6.1 and Standby/Slave PostgreSQL database version 10.17.
Hi, Is replication possible between PostgreSQL support Master database running 9.6.1 version which is a lower version to Standby/Slave running version 10.17? I have a datacenter *DC1* which is a production environment and serves live customer traffic hosting Master PostgreSQL database version *9.6.1* and Standby/Slave PostgreSQL database version *9.6.1* for storing analytics data. I am adding the new datacenter *DC2* by referring to https://docs.apigee.com/private-cloud/v4.51.00/adding-data-center?hl=en which will install *another additional* *Standby/Slave PostgreSQL database version* *10.17*. More details :- https://docs.apigee.com/release/supported-software Please guide me. Thanks in advance. Best Regards, Kaushal
Error : /usr/local/share/lua/5.1/pgmoon/init.lua:211: don’t know how to auth: 10
Hi,
I am running CentOS Stream release 8 with kong-2.6.0-1.x86_64 with the
below PostgreSQL database versions.
postgresql14-libs-14.1-1PGDG.rhel8.x86_64
postgresql14-14.1-1PGDG.rhel8.x86_64
postgresql14-server-14.1-1PGDG.rhel8.x86_64.
#kong migrations bootstrap -c /etc/kong/kong.conf --vv
> 2021/12/13 10:44:57 [verbose] Kong: 2.6.0
> 2021/12/13 10:44:57 [debug] ngx_lua: 10020
> 2021/12/13 10:44:57 [debug] nginx: 1019009
> 2021/12/13 10:44:57 [debug] Lua: LuaJIT 2.1.0-beta3
> 2021/12/13 10:44:57 [verbose] reading config file at /etc/kong/kong.conf
> 2021/12/13 10:44:57 [debug] reading environment variables
> 2021/12/13 10:44:57 [debug] admin_access_log = "logs/admin_access.log"
> 2021/12/13 10:44:57 [debug] admin_error_log = "logs/error.log"
> 2021/12/13 10:44:57 [debug] admin_listen = {"127.0.0.1:8001 reuseport
> backlog=16384","127.0.0.1:8444 http2 ssl reuseport backlog=16384"}
> 2021/12/13 10:44:57 [debug] admin_ssl_cert = {}
> 2021/12/13 10:44:57 [debug] admin_ssl_cert_key = {}
> 2021/12/13 10:44:57 [debug] anonymous_reports = true
> 2021/12/13 10:44:57 [debug] cassandra_contact_points = {"127.0.0.1"}
> 2021/12/13 10:44:57 [debug] cassandra_data_centers = {"dc1:2","dc2:3"}
> 2021/12/13 10:44:57 [debug] cassandra_keyspace = "kong"
> 2021/12/13 10:44:57 [debug] cassandra_lb_policy = "RequestRoundRobin"
> 2021/12/13 10:44:57 [debug] cassandra_port = 9042
> 2021/12/13 10:44:57 [debug] cassandra_read_consistency = "ONE"
> 2021/12/13 10:44:57 [debug] cassandra_refresh_frequency = 60
> 2021/12/13 10:44:57 [debug] cassandra_repl_factor = 1
> 2021/12/13 10:44:57 [debug] cassandra_repl_strategy = "SimpleStrategy"
> 2021/12/13 10:44:57 [debug] cassandra_schema_consensus_timeout = 1
> 2021/12/13 10:44:57 [debug] cassandra_ssl = false
> 2021/12/13 10:44:57 [debug] cassandra_ssl_verify = false
> 2021/12/13 10:44:57 [debug] cassandra_timeout = 5000
> 2021/12/13 10:44:57 [debug] cassandra_username = "kong"
> 2021/12/13 10:44:57 [debug] cassandra_write_consistency = "ONE"
> 2021/12/13 10:44:57 [debug] client_body_buffer_size = "8k"
> 2021/12/13 10:44:57 [debug] client_max_body_size = "0"
> 2021/12/13 10:44:57 [debug] client_ssl = false
> 2021/12/13 10:44:57 [debug] cluster_control_plane = "127.0.0.1:8005"
> 2021/12/13 10:44:57 [debug] cluster_data_plane_purge_delay = 1209600
> 2021/12/13 10:44:57 [debug] cluster_listen = {"0.0.0.0:8005"}
> 2021/12/13 10:44:57 [debug] cluster_mtls = "shared"
> 2021/12/13 10:44:57 [debug] cluster_ocsp = "off"
> 2021/12/13 10:44:57 [debug] cluster_v2 = false
> 2021/12/13 10:44:57 [debug] database = "postgres"
> 2021/12/13 10:44:57 [debug] db_cache_ttl = 0
> 2021/12/13 10:44:57 [debug] db_cache_warmup_entities = {"services"}
> 2021/12/13 10:44:57 [debug] db_resurrect_ttl = 30
> 2021/12/13 10:44:57 [debug] db_update_frequency = 5
> 2021/12/13 10:44:57 [debug] db_update_propagation = 0
> 2021/12/13 10:44:57 [debug] dns_error_ttl = 1
> 2021/12/13 10:44:57 [debug] dns_hostsfile = "/etc/hosts"
> 2021/12/13 10:44:57 [debug] dns_no_sync = false
> 2021/12/13 10:44:57 [debug] dns_not_found_ttl = 30
> 2021/12/13 10:44:57 [debug] dns_order = {"LAST","SRV","A","CNAME"}
> 2021/12/13 10:44:57 [debug] dns_resolver = {}
> 2021/12/13 10:44:57 [debug] dns_stale_ttl = 4
> 2021/12/13 10:44:57 [debug] error_default_type = "text/plain"
> 2021/12/13 10:44:57 [debug] go_plugins_dir = "off"
> 2021/12/13 10:44:57 [debug] go_pluginserver_exe =
> "/usr/local/bin/go-pluginserver"
> 2021/12/13 10:44:57 [debug] headers = {"server_tokens","latency_tokens"}
> 2021/12/13 10:44:57 [debug] host_ports = {}
> 2021/12/13 10:44:57 [debug] kic = false
> 2021/12/13 10:44:57 [debug] log_level = "notice"
> 2021/12/13 10:44:57 [debug] lua_package_cpath = ""
> 2021/12/13 10:44:57 [debug] lua_package_path = "./?.lua;./?/init.lua;"
> 2021/12/13 10:44:57 [debug] lua_socket_pool_size = 30
> 2021/12/13 10:44:57 [debug] lua_ssl_protocols = "TLSv1.1 TLSv1.2 TLSv1.3"
> 2021/12/13 10:44:57 [debug] lua_ssl_trusted_certificate = {}
> 2021/12/13 10:44:57 [debug] lua_ssl_verify_depth = 1
> 2021/12/13 10:44:57 [debug] mem_cache_size = "128m"
> 2021/12/13 10:44:57 [debug] nginx_admin_client_body_buffer_size = "10m"
> 2021/12/13 10:44:57 [debug] nginx_admin_client_max_body_size = "10m"
> 2021/12/13 10:44:57 [debug] nginx_admin_directives =
> {{name="client_max_body_size",value="10m"},{name="client_body_buffer_size",value="10m"}}
> 2021/12/13 10:44:57 [debug] nginx_daemon = "on"
> 2021/12/13 10:44:57 [debug] nginx_events_directives =
> {{name="worker_connections",value="auto"},{name="multi_accept",value="on"}}
> 2021/12/13 10:44:57 [debug] nginx_events_multi_accept = "on"
> 2021/12/13 10:44:57 [debug] nginx_events_worker_connections = "auto"
> 2021/12/13 10:44:57 [debug] nginx_http_client_body_buffer_size = "8k"
> 2021/12/13 10:44:57 [debug] nginx_http_client_max_body_size = "0"
> 2021/12/13 10:44:57 [debug] nginx_http_directives =
> {{name="client_max_body_size",value="0"},{name="client_body_buffer_size",value="8k"},{name="ssl_protocols",value
Recommended Hardware requirements for PostgreSQL DB Server.
Hi, Are there any recommended hardware requirements to setup PostgreSQL DB server? I checked https://wiki.postgresql.org/wiki/Database_Hardware but unable to find it. I am going to install Postgresql 9.6 version in AWS ( https://aws.amazon.com/ec2/instance-types/). I will appreciate if someone can suggest how many CPU cores, physical memory and disk space is required for the setup. Thanks in Advance and i look forward to hearing from you. Best Regards, Kaushal
