• While most modern school reforms argue that good schools can fix academic barriers kids face at home, many experts worry that investing in childhood education is not enough for society’s poorest children and families. Studies show that if parents’ education or job level is raised, the success of the child is raised, too. Through dual-generation strategies – programs that teach.
• Playlist: Two-way Street: Moving music off the iPod. Updating your iPod software. IPods run on software that’s specially designed to work with either a PC or a Mac, so converting an iPod between computer platforms requires you to update this software to work with the new operating system.

• Download New Mac Os
• New Mac Os 11
• Two Way Street Mac Os Catalina

Interviewing is a Two-Way Street, with Michelle Neal. Airdate: February 20, 2019. Mac Prichard: Hi, Mac, from Mac’s List here. Today’s episode is brought to you by Jobscan, the online tool that optimizes your resume and boosts you chances of landing an interview. Jobscan also offers a 10% discount to our listeners. The latest version of the tool at the time of this writing is v1.20.0. PgAdmin is available for Windows, Mac OS and desktop distribution of Linux like Fedora. Downloading pgAdmin for Windows and installing it is a fairly straightforward process like any other Windows app, so we won't cover it here. The beauty of this method is that it’s a two-way street. A shared folder lets you grab files from either your Mac or your PC. Not only does it make transferring files from an old machine quick (especially via Ethernet) and easy, but it also creates a useful repository for a small office that has some workers on Macs and other on PCs.

By: Sadequl Hussain Updated: 2015-06-25 Comments (5) Related: 1 2 3 More >Other Database Platforms

Problem

Scenario 1: We have a PostgreSQL instance running on our network. We are trying tomove away from PostgreSQL and want to accessthe data from our new SQL Server environment. We need to migratethe databefore the box can be shut down. Is there a simple way todirectly read data from that server while we build our new database in SQLServer?

Scenario 2: We have a third-party application that runs on SQLServer. We are predominantly a Linux shop and that's one of our few Windowsservers. The vendor has recently released a Linux version of their product andwe are thinking about Postgres. We were wondering if there's a simpleway of accessing SQL Server data from PostgreSQL.

Solution

In a previous MSSQLTip article, authorDaniel Calbimonte had shown ushowSSIS can be used to import data from a PostgreSQL instance into SQL Server. Thedriver that sat between SQL Server and Postgres in that example was an OLEDB provider,a commercial product from PGNP. Also we saw the PostgreSQL instance was hostedin a Windows server. In most commercial environments, people tend to runPostgreSQL in *nix-like operating systems like Linux. This makes the accesspattern a bit more involved.

On other hand, accessing SQL Server data from PostgreSQL is not an uncommon scenario:it's a questionoften raised by our community.

In this three-part series, we will create a very simple databaseinfrastructure with one SQL Server instance and one PostgreSQL instance. We willthen see how one server's data can be accessed by the other, effectivelycreating a two-way street.

This installment of the series will focus on creating the infrastructure andensuring a communication channel between the two machines. In the second part, we will see how SQLServer can make use of a linked server talking to a native PostgreSQL ODBCdriver. Granted, ODBC is not the best method of accessing remotedata where speed is the main concern, but at least the driver is free and if you are nottoo worried about data transfer speeds, you can give it a try. In the third andfinal part, we will cover PostgreSQL's Foreign Data Wrapper (FDW) feature andsee how it can be used to access SQL Server data.

Assumptions

Before going further, let's get a clear picture of our database environment:

• We are running a SQL Server 2012 64-bit Standard Edition instance on a Windows 2012 server.
• Our PostgreSQL version is 9.3. The instance is running on a Red HatEnterprise Linux (RHEL) 7.1 system.
• We will install a sample database called World in our PostgreSQL box. Wewill talk about this database shortly.
• We will create a small database in SQL Server and import one or moretables from the World database into it.
• We will install the AdventureWorks sample database in our SQL Serversystem.
• We will create a small database in PostgreSQL and import one or moretables from AdventureWorks into it.
• We are running both database servers in AmazonWeb Service (AWS) Elastic Compute Cloud (EC2) platform. In your case youcould be running both the servers in AWS, another cloud platform, entirelyon-premises or even in a hybrid setup. You could also be running both themachines in virtual servers hosted in your laptop.
• We are using SQL Server Management Studio (SSMS) to connect to the SQLinstance.
• We are using pgAdmin-III to connect to the Postgres instance.

Preparing the Database Infrastructure

Network Configuration

For the SQL and Postgres machines to communicate, first they need to be ableto reach each other over the network. This means they can be in the same LANsegment, or be able to connect over the WAN as part of a VPN setup, or evenrun in the same host machine as two local virtual servers. Whatever it is, theyneed to at least 'see' each other by ping-ing each machine. That's the firstthing we need to ensure.

In our current setup, we are running both the machinesin AWS, in the same VPC (Virtual Private Cloud), but in different subnets thatare physically separated in two different Availability Zones. That means their IP addresseswill be from different ranges (10.5.1.0/24 and 10.51.2.0/24 in this case as shown in theimage below). However, both the subnets are part of the same VPC - and thereforepart of a bigger IP address range - and they share the same network route tablethat allows them to communicate with each other.

You might beconnecting your on-premises server to a cloud-hosted box. If that's the case,the on-premises box will need to be part of your VPN network that connects to thecloud instance and vice versa. If you are running both machines in yourcorporate network, that's great. Chances are, you probably don't have to worryabout network configuration much. If the network part is too unknown for you tofigure out, have a chat to yournetwork guys. And finally, if you are following this article with two virtual servershosted in your local machine, no need to do anything at all. The machines willbe part of the same network you are connecting to the Internet with.

Firewall Configuration

Next, we need to ensure that any firewall running in either machine is allowing trafficfrom the other end. So the PostgreSQL server should allow traffic coming to Port5432 from the SQL Server box (or whatever port we have chosen Postgres to run on) and the SQL box needs to allow traffic coming toPort 1433 from the Postgres system (or whatever port SQL Server is running on).

Firewalls can be configured at multiple levels and you need to find out whatapplies in your case. It could be enforced via hardware appliance,via network rules or even at the operating system level. The ports you need toallow need to be opened at each of these layers.

In our setup for this article, we had to open up ports at three levels:

Port Changes - 1

At the network level, where AWS security groups control traffic going to theinstance. We created two security groups for our machines, each withdifferent set of rules. For those who don't know, AWS security groups arehigh-level virtual firewalls that can be configured to allow traffic eithercoming in or going out of a server. A security group can bedefined only once and 'attached' to multiple boxes, meaning each machinewill have the same firewall rules applied to it. Machines belonging to thesame security group will allow traffic from other boxes in that group. ForVPC based systems you can modify security group rules anytime withoutrebooting the machines.

The following images show two security groups (SG) we created. One is forthe Postgres server and the other is for the SQLServer box. You can see the SG_Postgres security group allows SSH (this lets us administer the serverremotely), traffic coming to Port 5432 (forPostgreSQL) and any traffic from the other security group. SG_Postgresallows any traffic to leave the box. Similarly, SG_SQL allows RDP (forremote access to the server), traffic coming to Port 1433 (for SQL Server)and any traffic coming from SG_Postgres. When we say 'any trafficcoming from the other security group' it means the boxes would beable to send out not only SQL traffic to each other, but communicateon any other protocol like ICMP (for ping), UDP, etc. You can tighten it downto certain ports only for security best practices - but fornow, we are keeping it all open between the two machines.

Again, this configuration doesn't apply if you are not using EC2 instancesin Amazon cloud.

Port Changes - 2

Next, we had to configure our OS-level firewall. Althoughour SQL Server machine had Windows Firewall on, we didn't have to configureit for anything. However, our Red Hat Enterprise Linux box was running thenewer Firewall daemon and we had to allow Postgres traffic through it. Ifthe Linux box in your setup is running older IPTables firewall, you need toconfigure IPTables to allow Postgres traffic.

The commands we ran in our Postgres Linux box were these:

This permanently adds port 5432 to the firewall rules. This allows us toremotely connect to the PostgreSQL instance from an application like pgAdminand it also allows SQL Server to connect to it.

This reloads the firewall daemon without restarting it. Next, it's time to allowthe SQL Server port:

Needless to say, you need your network or infrastructure team'sto helpmodify the firewall rules at any level if you don't have administrator or rootaccess to the systems.

Port Changes 3

The third change we made was at PostgreSQL configuration level. Postgres has its own built-inaccess rules and in the next section we will see how that's changed.

Once all the configurations are done, verify the machines can reacheach other via ping commands.

Install and Configure PostgreSQL

This section is here if you want to play with your owncopy of PostgreSQL and following this article as a tutorial. In a sense it'sgood because you can try out in your own setup before applying anything toproduction. If you are experienced and confident about making changes toyour existing Postgres environment, we recommend you still read through as we'lltalk about configuration later.

In the steps below we are showing how to install and configure PostgreSQL 9.3from scratch in an empty server. The commands are applicable for Red Hat-based systems like RHEL, CentOSor Scientific Linux. If you are using Debian-basd Linux distributions likeUbuntu, you need to use appropriate package installation commands like apt-get.We are also assuming you are using a terminal or console-based application likePuTTy to remotely log into the server using Secure SHell (SSH).

Step 1: Log in to your Linux server via SSH as the root user or a user with sudoprivileges.

Step 2: First, we will need to download the yum repository for PostgreSQL andfor that we will need the wget utility. Install the wget tool if it's notalready there.

Step 3: Download the PostgreSQL 9.3 RPM repository:

Step 4: Install the RPM repository:

Step 5: Install Postgres Server and its components:

Step 6: Initialize PostgreSQL (we assume you are using the default locationfor your data directory. Again this will be different for Debian-based Linuxdistributions) :

Step 7: Change some parameters in postgresql.conf

• Uncomment thelisten_addresses parameter and change its value from 'localhost' to '*', soit should look like listen_addresses=*. This will allow PostgreSQL to listen from any IPaddress, not just from the local host itself.
• Uncomment the port parameter so it looks like port=5432. This enables the default PostgreSQL port.

Save and exit the vi editor.

Step 8: Edit the pg_hba.conf file.

This is the part you need to consider even when making changes to an existing PostgreSQLinstance. That's because this is where you tell PostgreSQL to accept SQL Serve's traffic.At the end of the file,add one line for the IP address of your SQL Server machine:

Add one line for your local machine's network IP address range

Save the file and exit vi.

So what's happening here? Well, remember in the last section we talked about configuring Postgres' own internal firewall? That's thechange wejust made. pg_hba.conf is one of the most important PostgreSQL configurationfiles. It controls who can access the PostgreSQL instance running in themachine. The first parameter, 'host', specifies any access request coming from anon-localhost entity, i.e. from outside the box. The next parameter specifieswhich databases are allowed in the access request. We are specifying 'all' -meaning our SQL Server instance would be able to connect to anydatabase in the Postgres instance. The next parameter specifies the user accountto be used by the connection. Again we are setting this to 'all', meaning theconnection can use any valid user account within Postgres. Next comesthe IP address or the address range the connection can come from.In both cases the address/range needs to be specified in standard CIDR format.This means for a single IP address like our SQL Server instance this will be in the formXXX.XXX.XXX.XXX/32 and for any IP range it will be XXX.XXX.XXX.XXX/n - where ndefines the size of the network. Finally, we are specifying theauthentication method as 'trust'. By this we are saying the connection does notrequire an md5 checksum-ed password authentication.

Long story short, we have given our SQL Server box access to the PostgreSQL instance.

Step 9: Start PostgreSQL Service:

Step 10: Enable the service:

Step 11: Change the built-in postgres user account password. We can do this byfirst switching to the postgres user account and then invoking the psql command-lineutility. PostgreSQL's psql is similar to SQL Server's older osql/isql or newersqlcmd tool. Once logged into psql, change the password::

Now our PotgreSQL instance is ready.

Restoring a Sample PostgreSQL Database

We will now restore a sample database. Just likeSQL Server has its AdventureWorks sample database, Postgres has a number ofsample databases created by the user community. Some of these samples can befound in PgFoundry: a software repository for Postgres related tools and products. Wewill be downloading the world database from there and restoring it. World has alist of cities, countries and the languages spoken in each.

Step 1: Log into the PostgreSQL box again if you are not already logged inthere.

Step 2: Run the following command to download the compressed archive of theworld sample database:

Step 3: Next, decompress the tar archive file and copy it to the /tmpdirectory. Wewill also need to change the owner of the uncompressed directory and itscontentsto user postgres :

If you look under the /tmp/dbsamples-0.1/world directory now, you will seeit's got two files. One is a small README file that describes the sampledatabase andthe other is a SQL script file. This is the plain-text dump of the world database. Its purpose islike a SQL Server script file that generates a database structure and populatesit with data.

We won't look inside the file here, but import it directly in the next step.

Step 4: Switch to the postgres user

Step 5: Create a database called world. Note how we are embedding the 'CREATEDATABASE' SQL command as a parameter to psql.

Step 6: Import the world.sql file:

The SQL commands will flash and scroll on the screen as they are executedagainst the world database.

Step 7: Check from the command line if the tables have been created in theworld database:

This will show three simple tables:

It's not a huge sample database, but our first concern is toconnect to it from SQL Server, and it's sufficient for that purpose. As mentionedbefore, there are other sample databases for PostgreSQL that you can play with.One such sample database isSportsDB. Thisdatabase has a huge structure with many tables that you can experiment importinginto SQL.

Install pgAdmin-III to Manage PostgreSQL

SQL Server Management Studio (SSMS) is the front-end tool of choice for SQLServer DBAs and developers. Likewise, PostgreSQL admins and developers often usea tool called pgAdmin-III. This tool is freely available from both thePostgreSQL official site orpgadmin.org. Thelatest version of the tool at the time of this writing is v1.20.0. pgAdmin isavailable for Windows, Mac OS and desktop distribution of Linux like Fedora.

Downloading pgAdmin for Windows and installing it is a fairly straightforwardprocess like any other Windows app, so we won't cover it here.

What we will see in the image below is the dialog box that appears when youtry to connect to a PostgreSQL instance from pgAdmin.

In this case we are connecting to our PostgreSQL instance from outside thecloud as an Internet user (local computer not connected via VPN), so we have touse the machine's public IP address from AWS. The name field can have anymeaningful name, the host field should have the server's network name or IPaddress. The port is self-explanatory, we left the MaintenanceDB field to itsdefault value of postgres. We have decided to connect to the instance as thesuper user postgres (equivalent to SQL Server's 'sa' user) and decided pgAdmin shouldremember its password. We can also specify an existing or new server group toadd our instance to.

Once registered, you can view the Postgres instance from the navigation pane:

We can see our world database when we expand the Databases node. As we sawbefore, thereare three tables under the public schema.

Right clicking on any of the tables and choosing Scripts > CREATE Scriptoption from thepop-up menu will open a new query window with the CREATE TABLE command loaded init. We have created two scripts here: one for the city table and another forthe country table.

Now we need to create a corresponding city table in our destination database in SQL Server to import the data. There are differences between SQL Server and PostgreSQL data types and there are differences in their SQL syntax too, so the script we generated can't be directly run against SQL Server. Instead, we need to modify the script so it looks like this:

In the same way, the country table has the following generated script in pgAdmin:

We are changing this script as well:

Postgres' text data type is similar to varchar data type in SQL Server. We havechanged the data type 'character' to 'char', changed the way constraints are definedand did away with the owner and OID definition.

Obviously the city script needs to be run first as the foreign key from thecountry table depends on it. For now, let's save the modified scripts somewhere.We will come back to these in a short while.

Configure SQL Server Instance

Being in a SQL Server community site, we won't be taking the readers throughSQL Server installation process. We assume you know it very well.

We have installed a SQL Server 2012 64-bit Standard Edition instance onour Windows Server 2012 box.

We then connected to the database instance from Management Studio and created a small database called world.We ran themodified city script and then the country script in that database. The newly created tablesare shown below:

That's it. We are now ready to import data into our SQL tables from Postgres.

Download New Mac Os

Conclusion

In this first installment of the three-part series, we have created atwo-node, heterogeneous database infrastructure. We have established a communicationchannel between the two machines, have installed a PostgreSQL instance andconfigured it to accept SQL Server traffic. Along the way we have seen a newdatabase client tool called pgAdmin. We have touched on data type differencesbetween the two platforms and saw how there should be a data mappingexercise present in real-life migrations.

All that remains now are the actual data access methods from both ends.That's what we will see in next two parts.

Next Steps

• Stay tuned for the next two parts of this series
• Try and practice installing and configuring PostgreSQL in a stand-alonemachine
• Visit PostgreSQL official website for more information
• Check the network setup in your own production environment when planningformigrations. Chat to your network administrators about configurations,firewall rules, etc.

About the author

New Mac Os 11

Two Way Street Mac Os Catalina