radityopw

kumpulan query utilitas untuk postgresql

radityopw

#!/bin/bash

rm temp.duckdb

duckdb temp.duckdb <<-EOL

INSTALL postgres;
LOAD postgres;
ATTACH 'dbname=Adventureworks host=127.0.0.1 user=postgres password=postgres' AS db (TYPE POSTGRES, READ_ONLY);
SELECT count(*) as jml FROM db.sales.customer;

radityopw

-- copy dari c:\postgresql\12-backup\base ke c:\PostgreSQL\12-radit-test
-- ubah postgresql.conf
-- ganti port (saya ganti ke 5433)
-- matikan archive wal 
-- comment tentang wal 

restore_command = 'copy "C:\\PostgreSQL\\12-backup\\archive\\%f" "%p"'

--jika PITR (point in time recovery) isi recovery_target_time = '' misalnya 2023-02-23 14:32:00

radityopw

1. tools ini melaklukan backup pada database (SQLServer) user (selain sys database) di server windows ke server SFTP berbasis linux


kebutuhan : 
1. PHP : php.net
2. sqlcmd : https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16
3. bcp : https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16&tabs=windows
4. pscp : bagian dari putty (https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html)
5. script untuk transfer login : https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances
6. aws cli : https://min.io/docs/minio/linux/integrations/aws-cli-with-minio.html

radityopw

-- postlinks

TRUNCATE data_load;

copy data_load FROM 'C:\workspaces\stackoverflow-postgresql\PostLinks.xml';

INSERT INTO postlinks (
    Id, creationdate, postId, relatedPostId, LinkTypeId)
SELECT
     (xpath('//@Id', x))[1]::text::int

radityopw

FROM alpine:3.18

LABEL author="Radityo P W ([email protected])"

RUN apk update
RUN apk add apache2 nano
RUN apk add php81 php81-apache2 php81-common php81-curl php81-mysqli php81-sqlite3 php81-ffi

RUN rm -rf /var/cache/apk/*

radityopw

FROM alpine:3.18

LABEL author="Radityo P W ([email protected])"

RUN apk update
RUN apk add apache2 sqlite mysql-client nano curl build-base unixodbc-dev freetds-dev linux-headers
RUN apk add php81 php81-apache2 php81-bcmath php81-bz2 php81-calendar php81-common php81-ctype php81-curl php81-dba php81-dev php81-doc php81-dom php81-embed php81-enchant php81-exif php81-ffi php81-fileinfo php81-fpm php81-ftp php81-gd php81-gettext php81-gmp php81-iconv php81-imap php81-intl php81-ldap php81-litespeed php81-mbstring php81-mysqli php81-mysqlnd php81-odbc php81-opcache php81-openssl php81-pcntl php81-pdo php81-pdo_dblib php81-pdo_mysql php81-pdo_odbc php81-pdo_pgsql php81-pdo_sqlite php81-pear php81-pgsql php81-phar php81-phpdbg php81-posix php81-pspell php81-session php81-shmop php81-simplexml php81-snmp php81-soap php81-sockets php81-sodium php81-sqlite3 php81-sysvmsg php81-sysvsem php81-sysvshm php81-tidy php81-tokenizer php81-xml php81-xmlreader php81-xmlwriter php81-xsl php81-zip php81-pecl-redis php81-pec

radityopw

FROM ubuntu:20.04

LABEL author="Radityo P W ([email protected])"

ARG DEBIAN_FRONTEND=noninteractive

# UPDATE PACKAGES
RUN apt-get update

# INSTALL SYSTEM UTILITIES

radityopw

-- pilih database dan cek apakah ada perbedaan user dengan login
USE [nama database];
EXEC sp_change_users_login 'Report';
 
-- jika user dan login memiliki nama yang sama, gunakan autofix
 
EXEC sp_change_users_login 'Auto_Fix', '<nama user>';
 
-- jika user dan login memiliki nama yang berbeda, lakukan penyematan ulang
 

radityopw

FROM ubuntu:20.04

LABEL author="Radityo P W ([email protected])"

ARG DEBIAN_FRONTEND=noninteractive

# UPDATE PACKAGES
RUN apt-get update

# INSTALL SYSTEM UTILITIES