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 =
>
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
